VBA - Traffic light Icons (Conditional Formatting) -Trigger from mixed text/number results

Please Login or Register  to view this content.

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"
• R

Result - Works, but doesn't allow trafflic light icon formatting. Only shading.


VBA????

d) VBA code Attempt

• Result - Same as above.
• N

B - 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

Please Login or Register  to view this content.

Thanks!

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code in [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here