Excel has a lot of options for formatting and highlighting certain cells in our range, table, or data set. We can use also use VBA to help us in certain instances.
In the example below, we will show how to use VBA to describe the cells based on their color.
Checking Cell Color
Since we decided to have a description of our cells based on their color, we will first set three colors that will be our indicators: red, yellow, and, green:
Every color in Excel is based on three colors: red, green, and blue (RGB in short). There are 255 variants for each one of these three colors, and we can combine them to get the color we want. After we click on cell A1, we will go to the Home tab, then find the Fill Color under Font:
In the dropdown, we need to choose More Colors:
On the window that appears, we will go to the Custom tab, and there we will see the combination of three colors used in our cell:
We can see the color model, and also the Hex code, which can also be used in VBA, but not in this example.
If Statement Based on the Cell Color
To create the IF statement that will rely on these colors, we need to use VBA. We will open the VBA by pressing ALT + F11 on our keyboard, and then choosing Insert tab >> Module in the window that appears:
We need to insert the function in the module, and the function will be as follows:
Function CellValue(rng As Range) As String
If rng.Interior.Color = RGB(255, 0, 0) Then CellValue = "Bad"
If rng.Interior.Color = RGB(255, 255, 0) Then CellValue = "Not bad, Not terrible"
If rng.Interior.Color = RGB(0, 176, 80) Then CellValue = "All good"
This function has only one parameter, and that is rng as Range, whose type will be a String. After that, we simply use the IF statement in the VBA to check the interior (background) color of our cells, and then we return a certain description- which will be a String.
As noticeable, to find the matching Interior color, we use RGB codes. We showed how to find adequate codes for the colors we used in the example. The red color will return “Bad”, yellow will return “Not bad, not terrible”, and for the green color, we will get “All good”.
This is what the code looks like in the module:
Now we have the function to use in our Workbook. We will simply insert a reference to any of the cells in range A1:A3:
Of course, this function only works for these three colors. If we had different colors, an error would be returned. We can always include more colors if needed.