1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Conditional formatting Excel

Discussion in 'Business Applications' started by scouse13, May 2, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. scouse13

    scouse13 Thread Starter

    Joined:
    Oct 29, 2009
    Messages:
    137
    as you can see from the attatched file, i have used conditional formatting to change the colour of a range of cells based on an entry made in cell E3,(with many thanks to ENT for his help), what i would like to do now, is be able to change the actual colour used based on an entry made in C3, the entry made in C3 would only change the colour used not alter the conitional format used for the entry made in E3, can this be done, or is it easier to put vba code within the sheet.
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    You have a number of color constants:
    vbBlack 0x0
    vbRed 0xFF
    vbGreen 0xFF00
    vbYellow 0xFFFF
    vbBlue 0xFF0000
    vbMagenta 0xFF00FF
    vbCyan 0xFFFF00
    vbWhite 0xFFFFFF

    Try this site: www.cpearson.com/Excel/Colors.aspx
    I got a lot of ideas here
     
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    BTW you mention range b10:b28 but what happens with b7:b:9 ????
    Is tank 1, 3 and 8 always yellow? and tank 4, 17 and 22 always green?
    I think vba will do it but I'll await your answer
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Try this one:

    I added a button, and I entered the values in column C, (now light grey, but you could make them white to be 'invisible'

    The vba code uses this row to calculate which range is applicable, defines that as MYTANK and then depending on the tank number will color this one accrodingly.

    Just add other tank nrs in the select statement and put your favourite color there and it's I think what you wanted.

    P.S. I forgot to remove the conditional format in the tank range, you can do that since the code will supply the format for the selected area
     

    Attached Files:

  5. scouse13

    scouse13 Thread Starter

    Joined:
    Oct 29, 2009
    Messages:
    137
    thanks keebellah thats more or less what i needed, i have used the dat validation tool to define what numbers can be entered in C, can the code be altered slightly so the show button is not required and it updates when the enter or tab keys are pressed.
    also you said more tank numbers can be altered to the function statement, if required, i take it i could also add more case statements if further colours are required?

    many thanks ( i have attatched the file again)
     

    Attached Files:

  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    We will have to use the Worksheet_Change option so that everytime you change or the tank value or the value in E3 the range is updated, but won't it be a little messy?
    That means if you change the tank the range changes and then again when you change the value in E3 it changes again.

    But it's your sheet.

    If you cant to adda more tanks with other colors then you have to add case statement vor each separate condition but that's simple now you have the code

    I'll send you a asample when I to the on change option.
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Here's your example back again.

    I editted so that when C3 or E3 are changed the tank changes
    I removed your conditional format because this wouldn't allow other colors
    I added the column with values under C again, only nog colored white font so they seem invisible.

    You could also protect the sheet, so that only the tank range is editable and the 2 cells
     

    Attached Files:

  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/920549

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice