Conditional formatting based on cell color

If you want to use conditional formatting where a criterion is a cell color, you can’t just use standard conditional formatting. You have to create a VBA function that will recognize color and apply adequate formatting.

In this tutorial, we are going to create a formula that will name a color based on the cell background.

Let’s take standard colors from the Excel palette. You can find them in Home >> Font >> Fill Color >> Standard Colors.

Let’s display them inside cells, from A2 to A11.

Now, let’s create a function that will identify colors. Open the VBA window by pressing Left Alt + F11.

Create a new module: right-click project >> Insert >> Class Module.

Function CellBackgroundColor(rng As Range) As String
    For Each elem In rng
        CellBackgroundColor = elem.Interior.Color
    Next elem
End Function

Start typing the function name inside column B2 and AutoFill the rest of the cells.

These numbers don’t tell us much. Let’s modify our function to apply names to these colors.

Function CellBackgroundColor(rng As Range) As String
    For Each elem In rng
        Select Case elem.Interior.Color
            Case Is = 192
                CellBackgroundColor = "Dark Red"
            Case Is = 255
                CellBackgroundColor = "Red"
            Case Is = 49407
                CellBackgroundColor = "Orange"
            Case Is = 65535
                CellBackgroundColor = "Yellow"
            Case Is = 5296274
                CellBackgroundColor = "Light Green"
            Case Is = 5287936
                CellBackgroundColor = "Green"
            Case Is = 15773696
                CellBackgroundColor = "Light Blue"
            Case Is = 12611584
                CellBackgroundColor = "Blue"
            Case Is = 6299648
                CellBackgroundColor = "Dark Blue"
            Case Is = 10498160
                CellBackgroundColor = "Purple"
        End Select
    Next elem
End Function