Article 29 – Traffic Lights Conditional Formatting for Project Status for Text R / A / G

We all know that Traffic Lights are great visuals to communicate Project Status. But, we also know that Conditional Formatting for Traffic Lights can be done only through Numbers not through Text.

We need a flexible system that if I enter R (for Red), A (for Amber) and G (for Green), the required Traffic Light should be displayed. (A for Amber can be substituted with Y for Yellow also). Below is a method to achieve the required Traffic Lights.

Note – In this article, I am talking about Traffic Lights but this can be extended to any other Conditional Formatting type.

The Excel file related to this article can be downloaded from RAG_CF_Spotlight.

Method 1 – Without VBA

In this method, you enter a negative number for R, 0 for A and any positive number and in place of numbers, you can display R, A & G along with Traffic Lights. If you do not want to enter numbers but R, A & G only, then jump to method 2 which is a VBA method. (Don't get intimidated by VBA, just follow the steps and nothing else)

1. Let's Assume that your project layout is as above picture. Select the required range in column B > Right click > Format Cells > Custom and put following custom formatting code –

[<0]"R";[>0]"G";"A"

2. Select your range in column B again and Home tab > Conditional Formatting > New Rule > Format all cells based on their values and choose values as given in Red Zone.

(Note – If you don't want to show RAG and only show Traffic Lights, you can check "Show Icon Only" next to Icon Style and below Reverse Icon Order")

THAT'S ALL. You just need to enter any negative number for R, 0 for A and any positive number for Traffic Lights.

Method 2 – With VBA

As mentioned earlier, this method has the advantage of entering R, A or G and display Traffic Lights. You need not enter numbers which is the limitation of Method 1.

1. Save your file as .xlsm
2. ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Double click on your worksheet name
5. Copy paste the below code in this (You may like to replace range B2:C10 as per your requirement)

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ExitSub
    If Intersect(Target, Me.Range("B2:C10")) Is Nothing Or Target = "" Or UCase(Target) Like "[!RAG]" Then Exit Sub
    Application.EnableEvents = False
    With Target
        Select Case UCase(.Value)
            Case "R": .Value = -1
            Case "A": .Value = 0
            Case "G": .Value = 1
        End Select
    End With
ExitSub:
    Application.EnableEvents = True
End Sub

6. Repeat Steps 1 & 2 of method 1.

THAT's ALL. You just need to enter R, A or G and you have your status.