The If Statement is Based on the Cell Color in Excel

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:

A picture containing timeline

Description automatically generated

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:

Graphical user interface, application

Description automatically generated

In the dropdown, we need to choose More Colors:

Graphical user interface, application

Description automatically generated

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:

Graphical user interface

Description automatically generated

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:

Graphical user interface, application, Word

Description automatically generated

We need to insert the function in the module, and the function will be as follows:

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:

Graphical user interface, text, application, email

Description automatically generated

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:

Graphical user interface, application

Description automatically generated

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.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Posted in vba