Often you may want to use an IF function in Excel with cell colors.
For example, you may want to create a function that returns a certain value if a cell has a green background color.
Fortunately this is easy to do with some VBA code in Excel and the following step-by-step example shows how.
Step 1: Enter the Data
First, let’s enter the following list of tasks in Excel that are color-coded based on whether they have been completed or not:
Step 2: Use VBA to Extract Color Code
Next, we will write a simple function in VBA to extract the color from each cell as an integer value.
To do so, click Alt + F11 to open the VB Editor. Then click the Insert tab and then click Module.
In the module window that appear, type the following code:
Function FindColor(CellColor As Range) FindColor = CellColor.Interior.ColorIndex End Function
This will create a custom function that we can use in Excel to extract the background color of any cell as an integer value.
The following screenshot shows how to do so in practice:
Once you’ve entered the code, feel free to close out of the VB Editor. The code will automatically be saved.
Step 3: Extract Colors from Cells
Next, let’s type the following formula into cell B2 to extract the background color from cell A2:
=FindColor(A2)
Then click and drag this formula down to each remaining cell in column B:
Column B now shows the background color (as an integer value) of each corresponding cell in column A.
Step 4: Use IF Function with Colors
Now that we have the background color of each cell as an integer value, we can simply use these integer values in an IF function.
For example, we can type the following IF function into cell C2 to return a value of “Yes” if the background color of cell A2 is green or “No” otherwise:
=IF(B2=35, "Yes", "No")
We can then click and drag this formula down to each remaining cell in column C:
We could also use the OR operator within the IF function to check for multiple conditions.
For example, we can type the following formula into cell C2 to return “Yes” if the color of cell A2 is green or yellow:
=IF(OR(B2=35, B2=19), "Yes", "No")
We can then click and drag this formula down to each remaining cell in column C:
The formula now returns “Yes” if the background color of the cell in column A is green or yellow, or a value of “No” otherwise.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
How to Filter by Multiple Colors in Excel
How to Sum by Color in Excel
How to Use IF Function with Text Values in Excel
