# Conditional Formating - trafic lights

#### Jana.Luo

##### Board Regular
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?

Jana

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### Sandeep Warrier

##### Well-known Member
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
125
221%
330
456%
551
648%
711
825
921%
1030
1156%
1251
1348%
1411
1525
1621%
1730
1856%
1951
2048%
2111
2215%
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

Code:
``=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

##### Board Regular
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

##### Well-known Member
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.

Last edited:

#### Jana.Luo

##### Board Regular

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

##### Well-known Member
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

##### Board Regular

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

##### Legend
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

##### Board Regular
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

##### Board Regular
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

Last edited:

Replies
1
Views
557
Replies
3
Views
55
Replies
1
Views
81
Replies
3
Views
188
Replies
3
Views
92

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,732
Messages
5,833,380
Members
430,206
Latest member
Sami Gaid

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back