Table of Contents
The use of an Excel formula to perform an action based on a cell’s color allows for efficient and automated data processing. By utilizing conditional formatting, the formula can be set to trigger a specific action, such as highlighting or deleting the contents of a cell, if the cell’s color is red. This allows for easy identification and handling of important or problematic data, improving the accuracy and effectiveness of data analysis and manipulation.
Excel Formula: If Cell Color is Red Then Do Something
Often you may want to use an IF statement in Excel to perform some action if a cell color is red.
Fortunately this is easy to do by creating a Defined Name in Excel that can check whether or not a cell is red.
The following example shows how to do so in practice.
Example: If Cell Color is Red Then Do Something in Excel
Suppose we have the following list of basketball players in Excel in which the red cells indicate that the player is an All-Star:
Suppose we would like to use an IF statement to check if each cell color in column A is red and return either “All-Star” or “Not All-Star” in column B.
To do so, we can click the Formulas tab along the top ribbon, then click the Name Manager icon:
In the new window that appears, click the New button in the top left corner:
In the new window that appears, type RedCell in the Name box, then type =GET.CELL(38,Sheet1!A2) in the Refers to box, then click OK:
Next, type =RedCell in cell B2, then click and drag this formula down to each cell in column B:
We can see that the formula returns a color code of 22 for each corresponding cell in column A that is red.
Now that we know which color code represents this shade of red, we can type the following formula into cell B2 to return “All-Star” if the corresponding cell in column A is red or “Not All-Star” otherwise:
=IF(RedCell=22, "All-Star", "Not All-Star")
We can then click and drag this formula down to each remaining cell in column B:
Column B now returns “All-Star” or “Not All-Star” depending on whether or not the corresponding cell in column A is red.
Note that in this particular example, the shade of red that we used had a color code of 22.
However, different shades of red will have a different color code, which is why we created the RedCell custom formula to actually extract the color code before we created the IF statement.
Additional Resources
The following tutorials explain how to perform other common operations in Excel: