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:
‘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.
Andrei
http://www.webxpert.ro
Entries (RSS)