Conditional Formating - trafic lights

[Jana.Luo]

Hi all,

I wanted to use 3traffic lights to do the conditional formatting for my data.

e.g:

25
21%
30
56%
51
48%
11

I only want to apply the rule to 21%,56% and 48%, not the others. But if i highlight the whole column, the rule would apply to all the data.

How can I do that correctly?
One way I can think about was I need to highlight those three % numbers instead highlight the whole column. But what if the numbers are too many?

Need your ideas! Thanks in advance!

Jana

 

[Sandeep Warrier]

Hi,

When you mean traffic light conditional format, do you mean the 1st instance of a %value will be red, the 2nd instance orange, the 3rd instance green, the 4th instance red again and so on?

If yes, then how about

Excel Workbook
	A
1	25
2	21%
3	30
4	56%
5	51
6	48%
7	11
8	25
9	21%
10	30
11	56%
12	51
13	48%
14	11
15	25
16	21%
17	30
18	56%
19	51
20	48%
21	11
22	15%
Sheet1

Excel 2003

You'll have 3 conditional format formulae.

Select the range (A1:A22 in this example). Go to Format | Conditional Formatting.

Select Formula Is from the drop-down and paste the following formula in it

=IF(COUNTIF($A$1:A1,"<=1")*(A1<=1)>3,IF(INT(MOD(COUNTIF($A$1:A1,"<=1")*(A1<=1),3))=0,3,INT(MOD(COUNTIF($A$1:A1,"<=1")*(A1<=1),3))),COUNTIF($A$1:A1,"<=1")*(A1<=1))=[COLOR=Red][B]1[/B][/COLOR]

and choose red from the format.

Click on the add button and follow the similar process. The last part of the formula should be 2 instead of 1. choose orange from the format. Follow the same steps to get the 3rd format, changing the last number to 3 and choosing green from the format.

 

[Jana.Luo]

Hi,

Thanks a lot for that solution, but I use excel 2007, not 2003. In 2007 excel, there are traffic lights option in the conditional formating.
Meaning, the red light for the numbers >= A, the yellow light for the numbers between A and B, and green light for the numbers between B and C.

Could you help me out by using those lights?

Thanks a lot!
Jana

 

[Sandeep Warrier]

Hi,

Thanks a lot for that solution, but I use excel 2007, not 2003. In 2007 excel, there are traffic lights option in the conditional formating.
Meaning, the red light for the numbers >= A, the yellow light for the numbers between A and B, and green light for the numbers between B and C.

Could you help me out by using those lights?

Thanks a lot!
Jana

Hi,

What are your A, B & C values in this case? On my limited testing in Excel 2007 I don't think it is possible to use the default traffic color scheme for this. It seems to consider all cells selected (unless you go and select only the relevant ones). I tried selecting the entire range and setting lowest value to 0, mid value to .5 and highest value to 1.... but it still considered the entire range and colored all values >1 in green.

I think you would need to use formulae to determine which cells to highlight. If your conditions are based on certain values, we can perhaps design a formula that will detect them.

 

[Jana.Luo]

Yes, I have got the same result as you had so far.

A, B and C value are just numbers: e.g.: 80%, 40%, 20% etc...

Could you make a special formula to do that?

Many thanks,

Jana

 

[Sandeep Warrier]

I could try, but you need to give me ranges. For example, what number range should be red? 0%-50% etc... what number range should be orange and what number range should be green...

If you try out the formula I posted in my earlier post, it highlights the 1st % value in red, the 2nd in orange, the 3rd in green and this cycles on.

 

[Jana.Luo]

The first rang is >=60%, the second rang is <60%, but >=30%, the third one is <30%

can i use the fomular when i use the traifc lights?

regards,
Jana

 

[lenze]

Have you tried just selectiong the cells with % (Use the CTRL key)and applying the CF??
Or if your pattern is as shown, you can add a test for Even or Odd Rows
=(MOD(Row(),2=0)*YourRule
lenze

 

[Jana.Luo]

Hi Lenze,

First, I have more than 5000 rows which is too hard to select the % numbers manually.

Second, the pattern is not always list the example you saw...

But do thank you for the suggestion!

Jana

 

[AusSteelMan]

Not that I have figured it out yet, but if there is a way that the cells formatted as a percentage could be selected by formula, then a modified Lenze suggestion would work.

Maybe a short code that uses a SpecialCells criteria to select all cells in a range / worksheet that have been Formatted as Percentage. Could then manually set the CF or maybe just incorporate into the code. (but I'm not that smart to do it yet)

ASM