IF Statement Based on Cell Color

IF-Statement-Based-on-Cell-Color-in-Excel

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 to start with is that you can only manipulate cells based on their color by using VBA. The default Excel functions don’t give direct access to the VBA object model. Although modern solutions based on LAMBDA exist, working with colored cells still requires 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 a cell’s color. The COLORMATCH function is part of our free add-in, which can be 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:

  1. Type the IF formula
  2. Add the COLORMATCH function
  3. =IF(COLORMATCH(cell1, cell2), “found”, “not found”)
  4. If the cell colors are the same, the result is “found”

The function compares the colors of two cells. If the colors match, it returns the TRUE boolean value. If the background colors of the two cells differ, it returns FALSE. This feature allows easy use in conjunction with the IF function.

Excel Formula based on cell color

In this example, I want to compare cells based on their fill color. The Price and Result columns will be used. When the cell color in the Price column is green, the goal is to increase the price by 20%. If it’s pink, the price will be decreased by 20%. For cells without any color, the price remains unchanged.

IF-Formula-Based-on-Cell-Color-in-Excel

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. To look deeper into this topic, visit our Excel Formula section! You will find more than 100+ practical examples.

Thank you for joining us today. If you’d like to improve your Excel skills quickly, visit our YouTube channel, where we post new content several times a week.

You can download the practice file here.

Additional resources: