VBA - Traffic light Icons (Conditional Formatting) - How to trigger from mixed results (text and number)

[phil34]

Hi,

Aim

I want to use traffic lights (less obtrusive than full shading) to highlight these 3 results in a column (range O6:O37 in my spreadsheet), creating an easy to see dashboard.

The traffic light to be in the same cell, not in an adjacent column.

Column Contents i.e. Format Desired Traffic Light
Any date* Number Green
Blank Cell Number/text Amber
"Rejected" Text Red

Attempts/Difficulties so far with "Edit Formatting Rule" box (Conditional Formatting)

a) To set the Conditional formatting I recorded a Macro following my mouse strokes:

• Conditional Foramtting/Manage Rules/Edit Rules/Edit Formatting Rule box
• Select a Rule Type" - I selected "format all cells based on their values".
• Icon set (traffic lights).
• Result - This seems to default to Applying the default Top third (green), Middle third (Amber), Bottom third (Red) split to the data they are considering (underlying number which Excel holds for a date). Cells with "Rejected" or just no text (blank) were just ignored.

b) To set the Conditional formatting I recorded the same Macro as above,

• Select a Rule Type - "format all cells based on their values"
• Icon set (traffic lights)
• but changed the "Type" for Textual results (blank) or "Rejected" to "Formula" telling it to apply amber/red respectively when cells contain these texts.
• Result - Same as above.

c) To set the Conditional formatting I recorded the same Macro as above,

• Select a Rule Type - "Use a formula to determine which cells to format"
• Result - Works, but doesn't allow trafflic light icon formatting. Only shading.

VBA????

d) VBA code Attempt

• Result - Same as above.
• NB - What does "Operator = 7" mean - which I turned off.

Please help!

My VBA code so far is below:

Usual format, I have highlighted comments/non-functioning code in green

'l SELECT RANGE
Range("O6:O37").Select

'2A TYPE OF CONDITIONS 'This next line says we WANT AN ICON SET FOR CONDITIONAL FORMATTING (implies 25/50/75 percentile split)
Selection.FormatConditions.<wbr>AddIconSetCondition


'3A Then we select WHICH ICON SET to use (3 Traffic lights) (I HAVE TURNED THIS PARA 3 OFF)
'Selection.FormatConditions(<wbr>Selection.FormatConditions.<wbr>Count).SetFirstPriority
'With Selection.FormatConditions(1)
'.ReverseOrder = False
'.ShowIconOnly = False
'.IconSet = ActiveWorkbook.IconSets(<wbr>xl3TrafficLights1)
'End With

'4A - Condition 1 of 3
With Selection.FormatConditions(1).<wbr>IconCriteria(1)
'Type of Condition
.Type = xlExpression
.Value = "=LEN(TRIM(O6))=0"
'.Operator = 7
.Icon = xlIconAmberTrafficLight
End With


'4A - Condition 2 of 3
With Selection.FormatConditions(1).<wbr>IconCriteria(2)
'Type of Condition
.Type = xlCellValue
.Value = "=0"
.Operator = xlGreater
.Icon = xlIconGreenTrafficLight
End With


'4A - Condition 3 of 3
With Selection.FormatConditions(1).<wbr>IconCriteria(3)
'Type of Condition
.Type = xlCellValue
.Value = "REJECTED"
'.Operator = 7
.Icon = xlIconRedTrafficLight



End With



Thanks!