Free Microsoft Excel 2013 Quick Reference

Traffic Lights - Conditional Formatting

Greetings,

I'm currently trying to use traffic lights to do the following for me in a particular cell:

1. Show a yellow light if the value of the cell is <100%
2. Show a green light if the value of the cell is = 100%
3. Show a red light if the value of the cell is >100%

Is this possible? I'm having a hard time getting around this one since the red light option is always less than your previous option (yellow).

Thanks in advance!


Post your answer or comment

comments powered by Disqus
I have a spreadsheet that I am trying to add traffic light conditional formatting to. I have used it in the past, but I am unsure as to how I will make this particualr scenario work.

Cell C14:T14 have a percentage of profit made

C14 is the percentage that we originally planned

If D14:T14 are = C14 +/- 2% I want a yellow traffic light
If they are Greater than 2% above C14 I want a Green Traffic Light
If they less than C14 - 2% I want them to be red.

Any help would be greatly appreciated.

I am producing a spreadsheet of qualification data for members of staff.

Column C5 is their name

Column F5 runs a vlookup on their name at an enternal sheet to find out the date their qualification expires and returns either the date of expiry, NQ, DD, CB or RE. NQ means that their qualification has expired (not qualified) and DD, CB and RE can be grouped together as this means a member of staff is exempt for any reason.

At cell E5 I want to add a single cell traffic light conditional formatting system to show:

Condition one - If F5 equals NQ - Cell shading RedCondition two - If F5 equals DD, CB or RE - Cell shading OrangeCondition three - If cell F5 equals any other condition - Cell shading GreenWhat at your thoughts ladies and gents? All help very gratefully received!

I'm creating a template to be used by users on Excel 2003, 2007 and 2010. I have Excel 2010 but a couple of the input users are still on 2003.

I need to create a series of traffic lights in red, green, amber, blue which appear down a column depending on a value which needs to be input and hidden (I'm using it as a dashboard indicator in a presentation).

Is this possible?

I've tried using icon sets to get the basic 3 colours and an arbitrary number to input but this looks rubbish in Excel 2003 and I can't get it to reference text rather than a greater than number scale.

I've also tried setting up something using Marlett or Wingding fonts using "equal to" but for some reason the fonts are disabled in normal CF input methods and I've not got VB to work.

Any help would be greatly appreciated as I'm rapidly going mad with this now!

Thanks

Does anyone know how to get this to work using the 3-light conditional
formatting in Excel 2007 using the reference to another cell? I need it
pretty much the same way as listed above, but it is not working for me.

Here is the set up I currently have.

ROW 1 MTD ACTUAL BUDGET DIFFERENCE
ROW 2 120 120 130 -10

I want the 3-Light Conditional Formatting Formula to be used on the MTD
column with this formula set up below.

GREEN LIGHT >= =D2=0
YELLOW LIGHT >= =D2=(-3)
RED LIGHT

Why is this not working? Any and every bit of help would be most appreciated!

Thanks,

nathan

Hi

New to the forum and I am so helping that one of you Excel Gods can show me the error of my ways...so I can go home some time this evening! I like my job...but this is getting crazy! Needless to say that I have been struggling for some time now...you could probably guess that I am generaly reluctant to ask for help---but, I now know...I need help desperately!

I know that this is probably pretty basic but it has me stumped.

Here is what I need to accomplish.

I have a template that generates a % based on inputs--lets say that this value is in cell K3. Depending on another criteria (that is user entered and is in J3) we have established acceptable ranges for the calculated %.

For example if cell J3 = A then if the % in cell K3 is less than 5% it is red, between 5 & 10% it is yellow and greater than 10% it is green. This stop light conditional formatting needs to take place in cell L3.

There are 12 different values that one can select from a drop down list for J3.

To make it simpler--I am posting an attachment.

Please help! I am using Excel 2003

Thanks in advance

Hi,

Thanks to some great help on this forum, I managed to get some code which lets me sort data which is arranged in groups of 3 rows. This data is a 'work in progress' tracker with different 'cases' and a range of dates across it which different stages of work have to be completed by. I have a hidden, blank formatted group of rows which is used in conjunction with a userform to add a new case to the tracker and within this is contained simple formulae to let the user see how many days they have to complete certain tasks. However, when I run my sorting macros, the data is sorted as I'd like but the formula copied relatively throughout is lost and I'm just left with values instead. Does anyone know why?

Code I have to sort the data is below (either by date or case number depending on the button pressed):


	VB:
	
 SortByGroups() 
     
    Dim Delimiter As String: Delimiter = Chr(5) 
    Dim Descending As Boolean 
    Dim rangeToSort As Range, imageToSort As Variant 
    Dim outputRange As Range, outputArray As Variant 
     
    Dim MixArray As Variant 
     
    Dim rowSize As Long, colSize As Long 
    Dim keyColumn As Long 
    Dim groupCount As Long 
    Dim rowStart As Long, rowCount As Long 
    Dim rowOut As Long, colIndex As Long 
    Dim i As Long, j As Long, pointer As Long 
     
    Rem adjust To meet the situation 
    Set rangeToSort = Sheet1.Range("A6").CurrentRegion 
    Set rangeToSort = rangeToSort.Resize(rangeToSort.Rows.Count - 5) 
    Set rangeToSort = rangeToSort.Offset(5, 0) 
    If Application.Caller = "Button 4" Then 
        keyColumn = 24 
    ElseIf Application.Caller = "Button 7" Then 
        keyColumn = 1 
    End If 
     
    Descending = False 
     'Code below if want to spit the sort out on a seperate bit of sheet
    Set outputRange = Sheet1.Range("ab6") 
     'Code below to put the sorted range in place of existing data (what I want eventually)
     'Set outputRange = rangeToSort
     
    rowSize = rangeToSort.Rows.Count 
    colSize = rangeToSort.Columns.Count 
    Set outputRange = outputRange.Resize(rowSize, colSize) 
     
    imageToSort = rangeToSort.Value 
    Redim MixArray(1 To rowSize) 
     
    Rem prepare data mix array 
    For i = 1 To rowSize 
        If StartsGroup(imageToSort(i, keyColumn)) Then 
            If 0 < pointer Then MixArray(pointer) = MixArray(pointer) & Delimiter & CStr(groupCount) 
            pointer = pointer + 1 
            groupCount = 1 
            MixArray(pointer) = Format(imageToSort(i, keyColumn), "yyyymmdd") & Delimiter & i 
        Else 
            groupCount = groupCount + 1 
        End If 
    Next i 
    Redim Preserve MixArray(1 To pointer) 
    MixArray(pointer) = MixArray(pointer) & Delimiter & CStr(groupCount) 
     
    Rem sort the mix array 
    Call sortQuickly(MixArray, Descending) 
     
    Rem mix array To Output array 
    Redim outputArray(1 To rowSize, 1 To colSize) 
    For i = 1 To pointer 
        rowStart = Val(Split(MixArray(i), Delimiter)(1)) 
        rowCount = Val(Split(MixArray(i), Delimiter)(2)) 
        For j = 1 To rowCount 
            rowOut = rowOut + 1 
            For colIndex = 1 To colSize 
                outputArray(rowOut, colIndex) = imageToSort(rowStart + j - 1, colIndex) 
            Next colIndex 
        Next j 
    Next i 
     
    Rem outputArray To worksheet 
    outputRange.Resize(UBound(outputArray, 1), UBound(outputArray, 2)) = outputArray 
     
     'Make sure all traffic light conditional formatting is done
    Call SetFormatConditions 
     
End Sub 
Function StartsGroup(aVal As Variant) As Boolean 
    StartsGroup = aVal  vbNullString 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and in another module...


	VB:
	
) 
    Dim pivot As Variant 
    Dim i As Long, pointer As Long 
    If low = 0 Then low = LBound(inRRay) 
    If high = 0 Then high = UBound(inRRay) 
     
    pointer = low 
     
    Call Swap(inRRay, (low + high) / 2, high) 
    pivot = inRRay(high) 
     
    For i = low To high - 1 
        If (inRRay(i) < pivot) Xor Descending Then 
            Call Swap(inRRay, i, pointer) 
            pointer = pointer + 1 
        End If 
    Next i 
    Call Swap(inRRay, pointer, high) 
    If low < pointer - 1 Then 
        Call sortQuickly(inRRay, Descending, low, pointer - 1) 
    End If 
    If pointer + 1

Dear Forum,

I have attempted to make a conditional format based on a cell contining the words `no comments`. If the cells has `no comments` I want to show the icon a red traffic light and if the comments are in place a green traffic light.
I tried many times for this process but can only return a green traffic light and tried many internet search but trouble finding an answer.
thank you for your kind help.
Nihongin

I have attached a worksheet with the narrative which forms the basis of my problem.
I am required to use Conditional Formatting which will be looking at single cells at month end.
At the end of the month I want to show the conditional formatting tool used with a traffic light color system, which is defined by using percentages.
I commence with a value at the end of Month 1
At the end of Month 2 the average value if greater than Month 1 the cell should simply be Red
If the value at Month end 2 is less than 5% of the end of Month 1 the cell should be Orange
If the cell value at Month end 2 is greater than 5% of the value of Month end 1 the cell color should be green
The cells in qustion arte shown on the attached worksheet.
Thanks for your support. Mike

Hi could someone please help.

I need to be able to colour a cell called status red amber or green depending on the time taken from date due to date completed.

To simplify the idea i have 4 cells in a row, A1 being the due date, B1 being the revised date, C1 being the completed date and D1 being the status cell (Green, Amber, Red, Green depending on status)

So a Due date is input into cell A1 and D1 will be green to start.(Colour cell D4 green and text GREEN)

If 7 days past the due date A1 will turn amber (Colour cell D1 yellow and text AMBER)

If past 7 days of the due date A1 then D1 turns to status red (colour cell D1 red and text RED)

If a revised date is input into cell B1 then D1 status should be reset to correspond to the conditions above again within 7 days or beyond.

When a date or the word completed is input into cell C1 then D1 should change to colour green and text green.

Can this be done with simple code using IF's and conditional formatting, any help appreciated.

Thank you in advance.

Stephen

Hi Each and All,

This is what i'm trying to achive,
I'm trying to create a kind of traffic light type of status indicator,
If cell AI70 then fill cell A3 green, I've achived this through conditional
formatting, But it would be even better if the cells A1-A3 were round in
shape, any ideas?

Repects

Hi

I have a spreadsheet I use for calculating target dates for issue of
student results.

At the top I have a cell containing todays date I1

One column contains the first date of the result issue process ie cell
number D6

Next column contains the target date for result issue - cell E6 which
is calculated by formula (20 workdays after the date in D6)

I need to set up a traffic light system in conditional formatting which
highlights the target date in E6, firstly amber when today(I1) is 9
workdays after the date in D6, and then a second condition turning to
red when today (I1) is 12 workdays after the date in D6.

Help - I am utterly confused!

Thanks

--
HDV
------------------------------------------------------------------------
HDV's Profile: http://www.excelforum.com/member.php...o&userid=26299
View this thread: http://www.excelforum.com/showthread...hreadid=467428

I’m trying to use the conditional formatting, traffic light feature
with dates and am finding the new Edit Formula Rule app to be rather
limiting. Hopefully it’s just my ignorance.

Cell E1 contains current date (updated when the workbook is opened)
several other cells have fixed expiration dates. In the expiration
date cells I want traffic lights indicating: red once the date matches
current, yellow seven days in advance, and green anytime the date in
the cell is greater than 7 days beyond the current date.

I can get red and green to work but getting yellow is a mystery.

Green value is set to “E1” and Type set to Number

Yellow says “when < formula and” then there is an option to select >
or “=>” and enter a value. In the value I have tried “=E1+7 just “=7”
etc but all I can accomplish is that yellow never works or will always
work when the date is exceeded with red never working.

I assume that yellows formula in “when < formula and” is referring to
the “value” for green even if number is selected for green instead of
“formula”. Makes me think that I could never create the condition I
want.

If I sound confused it’s because I am! The help feature does not
explain the feature that I can find
Any help will be appreciated.
Robert

I have a problem with conditional formatting when using percentages which I
will try to explain.

I am using "Format all cells based on their value"
Format Style = Icon Sets
Reverse Icon Order is selected.
Red traffic light when value is >= 90 percent
Orange traffic light when < 90 and >= 80 percent
Green traffic light when < 80

All cells have been formatted as percentage.

However the conditional formatting applied is not as expected.
A1 = 50%
Expected result = Green
Actual restul = Green

A2 = 80%
Expected result = Orange
Actual result = Red

A3 = 70%
Expected result = Green
Actual result = Orange

If I change this to numbers, then the results are correct.

Any thoughts appreciated

Caroline

I'm having problem with Conditional formatting in a pivot table.
The pivot is connected to an OLAP db (SSAS).

The values looks like this.
VALUE 45,00 28,20 47,25
Accepted VALUE 60,00 55,00 40,00
Critical VALUE 39,00 30,00 25,00

I'd like to set the conditional formatting on the VALUE.
Using "traffic lights" it should be green if it is larger than (>=) the
Accepted VALUE, yellow if it is larger than (>=) the critical and red if
lower.

I have tried with an icon set using the formulas
=INDIRECT("R[+1]C";FALSE)
and
=INDIRECT("R[+2]C";FALSE)
(setting the types to numbers) to get the two values below the VALUE in the
pivot.

This gives me the strangest bug.
The icon rule is formatted based on which cell I select, inside or outside
the pivot table (?).
If I select an empty cell outside the pivot they all turn green but if I
just enter random numbers outside the pivot and select the cell above or if I
select a cell inside the pivot with numbers in the cells below, the rule is
set according to the values.

Can anyone help me with this? Macro or formula, all tips are appriciated.
Thanks,
Mats M

PS. If it is to any help; The VALUE is a calculated member and the limits
are measures in the cube.

I have used the traffic light icon set in conditional formatting so that it
displays both the traffic light and the cells data.

Is it possible to format the traffic light so that it appears on the right
of the data instead of the left.

Regards

I have been using Icons from 2007 to display 'traffic' lights on a report- however I need to actually make this 2003 compatible - is there a way to use images (since the Icons don't exist) appear as part of conditional formatting in 2003?

Please could someone tell me how to apply to conditional formatting to a textbox on a worksheet when the contents of a particular cell is variable.

For example, I'm using a traffic light system (Red, Amber, Green), so if the content of cell P5 is one of these then the textbox background colour and font should change: Red with bold white font; Amber with black regular font and Green with black regular font.

Any help gratefully received.

I should add, after investigating this myself, that I am referring to the textbox on the Autoshapes toolbar and not the textbox used on userforms.

Thank you.

Hi,

I want to use the traffic light icons in the conditional formatting to create a basic dashboard.

What I want to do is for the Value Green - Show the Green Icon - for the Value Yellow - Yellow Icon etc.

I also have an Indirect Formula in the cell atm

=IF(INDIRECT("'"&$L$1&"'!F54")=""," ",INDIRECT("'"&$L$1&"'!F54"))

Which just essentially tells it which worksheet it should look in to pull out the values.

Thanks in advance.

hello there

i could really do with some help in creating a table that produces a
traffic light style way for showing performance in exam progress.

a red for underperformance - orange for working at capability and green for
better than expected performance.
the formating is related to the cells in the next columns - min perf & max
perf.

i've managed to do this using the conditional formatting option. however
not all pupils are involved in each subject and so i would like those cells
to stay unformatted (no fill) - with no luck so far. it seems that you can
only have 3 formatting options but i think i really need 4. Is there an
alternative way?

example is below

A B C D E
1 name current min perf max perf
2 mike thomas B C B -
cell C2 will be in orange
3 susie lowes D B
- cell C3 will be in red
4 rick
- cell C4 should remain unformatted
5 aaron fletcher A C C -
cell C5 will be in green

thanks for any help you can give me here

cheers
Chris Barnett
chrisbarnettspamoff@tiscali.co.uk

Hey guys

pls see attached -

The values in B6:F7 of this tab pertain to the matrix in the Communications tab. B13:E18

I would like to highlight the traffic lights above depending on whether or not the results in D14 of the communications tab meet the benchmark set in E14:E17.

Meeting or exceeding b'mark would be green, 5% under Amber 6% under Red.

Ive seen Andy Popes traffic light chart sheet but its whoosh over my head!

Maybe this is too difficult in which case could someone recommend an alternative?

Cheers

Hi everyone,

I have a conditional formatting problem.

The problem is that Excel always stop when it found a condition being "true".

I have table with a range of percentages and a "standard percentage value"
I now want to highlight all percentages into three traffic light colours depending on whether they are:
1. 80% greater or less than the standard value (RED)
2. 50% greater or less than the standard value (ORANGE)
3. If else then they are ok (GREEN).
in addition it would be nice to have those boxes which display 0 in blue.

I anyone could post a formula for that, that would be great.

Thanks everyone!

Hello everyone,

Im trying to get conditional formatting to look at a completion date in cell A5 and getting cell D5 to change colour depending on the length of time to cell A5.

What I would like is for D5 to change to red if there is 4 months or less to the completion date in A5.

D5 to change to orange if there is still 4 - 8 months from the completion date A5

D5 to change to green if there is still 8 - 12 months from the completion date A5.

Hopefully the formula will also work if cell D6 is looking at completion date in A6 with the same traffic light system and so on.

Many thanks in advance!!

Hi

I have a spreadsheet I use for calculating target dates for issue of student results.

At the top I have a cell containing todays date I1

One column contains the first date of the result issue process ie cell number D6

Next column contains the target date for result issue - cell E6 which is calculated by formula (20 workdays after the date in D6)

I need to set up a traffic light system in conditional formatting which highlights the target date in E6, firstly amber when today(I1) is 9 workdays after the date in D6, and then a second condition turning to red when today (I1) is 12 workdays after the date in D6.

Help - I am utterly confused!

Thanks

Hey all. Hoping that someone can shed some light on what I'm doing wrong here. I've tried everything I can think of and can't get the results I desire.

In the attached spreadsheet, I've got several transactions that I want the cell background changed based on whether they meet the following criteria:

IF $M2<Today()

AND $I2="Auto" or $I2="Home"

So basically, if the date in column M for each row is less than today's date, and the data in column I equals either Auto or Home, I need it to change the background color of the cell.

I've tried combining multiple formulas in the conditional formatting tool and can't get where I need to be. I can get it to highlight everything based on the date, or whether it's Auto or Home, but not in a way that the Date and the Auto/Home criteria are met. If the date is less than today, but the contents of say I5 is UMB, I don't want any formatting to take place.

Is this even possible, or am I just dreaming?


No luck finding an answer? You could always try Google.