If Cell Color Is Green Then Show or Customize Outputs in Excel

In the latest version of Excel, it is directly possible to use a specific cell color as the condition when using the IF function. But for previous versions, with a little help from Excel macros, there are some work-arounds allowing you to easily perform this task. In this tutorial, we are going to demonstrate what you can do with a cell value if the cell color is green or any other specific color in Microsoft Excel.

Defining a custom function with the help of the name defining feature will be a required step in all the operations we’ll perform below.

Example 1 – If Cell Color Is Green Then Set Cell Value

Let’s set cell values if the cell color is green in our sample dataset. From the dataset, we can see that our second and fifth entries are green. We want to associate “Absent” with them, and “Present” with the rest.

Let’s create the custom function to do this, then set the cell value if the cell color is green or any other color.

Steps to Define Custom Function:

Go to the Formulas tab on your ribbon.
Select Name Manager from the Defined Names.

The Name Manager box will open up. Now click on New on top of the box.

In the New Name box, enter a name for the function in the Name field.
In the Refers to field, enter the following:

=GET.CELL(38,Sheet1!B5)

Click on OK.

Now we have a formula called GreenCheck ready that can be used for other purposes.

Steps to Set Values:

To find out the color code of green (or any other color), select cell C5 and enter the following formula:

=GreenCheck

Press Enter and click and drag the Fill Handle to the end to find values for all.

We can see the color code of green is 50.

Clear the values you just entered.
Select cell C5 now and enter the following formula:

=IF(GreenCheck=50,"Absent","Present")

Press Enter.

Select the cell again. Click and drag the Fill Handle icon to the end of the list to replicate the formula for the rest of the cells.

Example 2 – If Cell Color Is Green Then Modify Cell Value

Let’s say there are values already associated with the cells, that we now want to modify.

Steps to Define Custom Function:

Go to the Formulas tab on your ribbon.
Select Name Manager.

The Name Manager box will open up. Click on New on top of the box.

In the New Name box, write down a name for the function in the Name field.
In the Refers to field, enter the following:

=GET.CELL(38,Sheet1!B5)

Click on OK.

Now we have the formula called GreenCheck ready that can be used for other purposes.

Steps to Modify Cell Values:

Let’s make a chart similar to the one in the previous Example.

Select cell C5 and enter the following formula:

=IF(GreenCheck=50,"Absent","Present")

Press Enter.

Select the cell again. Now click and drag the Fill Handle icon to the end of the list to replicate the formula for the rest of the cells.

Now let’s modify these values. Let’s say, we want the third entry to be green too.

Select cell B6.
Go to the Home tab of your ribbon.
Select Format Painter from the Clipboard group.

Click on cell B7.

The value of cell C7 will now automatically change to “Absent”.

Example 3 – If Cell Color Is Green Then Remove Cell Value

Now let’s say there are values already set on the dataset and we want to remove them based on their color. As before, first, we need the custom function.

Steps to Define Custom Function: