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.
|
1 2 3 4 5 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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 |