When Conditional Formatting Simply Won’t Do

Excel has many neat features, Conditional Formatting being one of them. But one particularly irritating feature of Conditional Formatting is that you can only cater for a maximum of three possible states.

So, what do you do if, for example, you have a list of five possible strings that the user can enter, and you want to apply visual styles for each of the different strings? Well, you’ll need to roll up your sleeves and get stuck into VBA. Fortunately, it really is quite straightforward.

Conceptually, what you want to do is handle the ‘Change’ event on the active Worksheet – when the user changes a value, you want a piece of code to be executed. Here’s how:

1. Open up the VBA editor for the Excel workbook using Alt+F11 (or go to Tools > Macro > Visual Basic Editor)
2. In the Project explore, under VBA Project ([Workbook Name]) > Microsoft Excel Objects, open the worksheet object for the relevant worksheet by doubling-clicking (or right-clicking and selecting View Code)
3. Paste in the following code and change to suit your needs
4. You can test the code by returning to the Excel workbook

Private Sub Worksheet_Change(ByVal Target As Range)
   'Test that the Target is within the range of columns
   If Target.Column >= 2 And Target.Column <= 11 Then
        'Test that the Target row is correct
       If Target.Row = 15 Then
           'Handle the N/A case
           If Target.Value = "Not Applicable" Then
                Target.Font.Bold = False
                Target.Interior.Color = ColorConstants.vbWhite
           'Handle the Normal case
           ElseIf Target.Value = "Normal" Then
                Target.Font.Bold = False
                Target.Interior.Color = ColorConstants.vbGreen
           'Handle the Warning case
           ElseIf Target.Value = "Warning" Then
                Target.Font.Bold = True
                Target.Interior.Color = ColorConstants.vbYellow
           'Handle the Alert case
           ElseIf Target.Value = "Alert" Then
                Target.Font.Bold = True
                Target.Interior.Color = ColorConstants.vbRed
           ElseIf Target.Value = "Excellent" Then
                Target.Font.Bold = True
                Target.Interior.Color = ColorConstants.vbBlue
           'User has input an invalid string
           Else
                'Declare a response variable
               Dim Response
               'Show a message box
               Response = MsgBox("You must enter a valid status.", vbOKOnly, "Status Error")
           End If
        End If
    End If
End Sub

Et vóila! Share and enjoy.

Note: These instructions and the associated code are provided ‘as-is’, with no warranty, implied or otherwise, and is used at the reader’s own risk.