Ever wondered how to find the background color for a specifil excel cell? Why? Because you need to filter the sheet by color, and the only way that excel can filter is by value. So, instead of color, you need to save the color name in a separate column.

Excel doesn’t offer a function for this, so you must use VBA.
To start the VBA editor, press Alt+F11, then create this function:

Function GetBgColor(rCell As Range)
    ‘declare color variable
    Dim strColor As String

    ‘switch cell background color
    Select Case rCell.Interior.ColorIndex
       Case 1
        strColor = "Black"
       Case 6
        strColor = "Yellow"
       Case 2
        strColor = "White"
       Case Else
        ‘the color has no Index
        strColor = "No fill"
    End Select
    ‘return color name
    GetBgColor= strColor
End Function

Save this function by pressing Alt+Q, and return to your worksheet. Now, in a separate column, start a new formula as


where A2 is the cell that you want to query.

Here’s a screenshot:

You can improve this function in order to test multiple colors, but this is the template.


Leave a Reply