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

=GetBgColor(A2)

where A2 is the cell that you want to query.

Here’s a screenshot:
Screenshot

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

Andrei
http://www.webxpert.ro


Leave a Reply