Conditional Formatting

[dmed99]

Having trouble using the conditional formatting section on Excel 2010.

I am trying to use the red, yellow, green traffic light to tell me that I am below, within, or projected over my target for the month. H6 is my projected target total. I have set up the equation for H16 that is where I am currently projecting for the month. But I can not get H16 to show the 3 colors to differentiate whether I am below 90%, equal to or greater than 90%, or above my H6 projection.

Any help with this would be appreciated.

Dan

[FDibbins]

Hi and welcome to the forum

You cannot use that type of CF for this. The traffic-light type of CF uses a range, and then compares each value within the range to all other values in that range, to determine which colors to apply. You cannot refer to another cell for comparison...
H16=10 - red
H17=20 - yellow
H18=30 - green

I would suggest that you use "use formula", then create 3 new rules (1 for each color), that would compare your 2 values

[dmed99]

Ford,

I read that I can not have a formula change a cell color, by what you are saying that is not true?

So let say H6 is 100k and H16 is projecting less than 90K so it should be red, how would that be written, then between 90k and 100k be yellow, and over 100k would be green.

This is by far the hardest thing that I can not figure out.

Dan

[popipipo]

Something like this?

[FDibbins]

Take a look at the attached. I have created 3 rules, using the "use rules" option.

Click on the cell (H16), then click on Home tab/Styles/Conditional Formatting, take a look atthe rules there. To keep them simple I set them in a specific order (otherwise I would have needed to use a more comlpex formula for the middle rule)

It does not give you the "traffic lights" like popip's VBA does, but it is simple and changes the cells color based on the values

[dmed99]

That's Awesome!!!! Your the best!!!

Thanks a million,

Dan

[dmed99]

Willem,

Thanks a million.

Dan

[dmed99]

This post has been solved.