In this tutorial, I’ll show you how to write a formula using an IF Statement based on cell color in Excel using the COLORMATCH function.
The most important thing we need to start with is that manipulating cells based on their color can only be performed with the help of VBA. The default Excel functions do not provide access to the VBA object model. Despite modern solutions based on LAMBDA, working with colored cells can currently only be handled with the help of a user-defined function. In this case, we will use the COLORMATCH function.
In this article, I will demonstrate how to write an IF statement based on the color of a cell. The COLORMATCH function is part of our free add-in, found here.
Steps to Create an IF Statement Based on Cell Color
Here are the steps to create an IF statement based on cell color:
- Type the IF formula
- Add the COLORMATCH function
- =IF(COLORMATCH(cell1, cell2), “found”, “not found”)
- If the cell colors are the same, the result is “found”
The function compares the colors of two cells. If a match is found, it returns the TRUE boolean value. If the background colors of the two cells are different, it returns FALSE. This feature makes it possible to easily use it in conjunction with the IF function.
Excel Formula based on cell color
In the example, I want to compare cells based on fill color. We use the Price and Result columns. We aim to implement that if the cell color in the Price column is green, then we increase the price by 20%. If the cell color is pink, then we decrease the current price by 20%. If the cell does not contain any color, we leave the original price unchanged.
Here is the IF formula that meets all conditions:
=IF(COLORMATCH($E$3,B3),B3*1.2,IF(COLORMATCH($E$4,B3),B3*0.8,B3))Conclusion
By using the COLORMATCH and IF functions together, you can perform useful calculations. If you would like to delve deeper into this topic, be sure to visit our Excel Formula section! You will find more than 100+ practical examples.
Thank you for joining us today. If you’d like to quickly improve your Excel skills, be sure to visit our YouTube channel, where we post new content several times a week.
You can download the practice file here.
