Conditional Formating - trafic lights

Jana.Luo

Board Regular
Joined
Jan 15, 2009
Messages
109
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:confused:
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Sandeep Warrier

Board Regularmmmm Pizza
Joined
Oct 31, 2008
Messages
2,672
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
Joined
Jan 15, 2009
Messages
109
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

Board Regularmmmm Pizza
Joined
Oct 31, 2008
Messages
2,672
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
Joined
Jan 15, 2009
Messages
109

ADVERTISEMENT

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

Board Regularmmmm Pizza
Joined
Oct 31, 2008
Messages
2,672
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
Joined
Jan 15, 2009
Messages
109

ADVERTISEMENT

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
Joined
Feb 18, 2002
Messages
13,690
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
Joined
Jan 15, 2009
Messages
109
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
Joined
Sep 4, 2009
Messages
208
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:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,533
Messages
5,511,889
Members
408,867
Latest member
Ranjeet yamgekar

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top