How to change value based on cell color in Excel?

You will find in this tutorial two easy ways to alter the background color of cells in Excel based on the value, and you will also learn how to utilize Excel formulae to change the color of blank cells or cells with formula mistakes.

To change the backdrop colour of a single cell or a range of data in Excel, all one needs to do is click the Fill color icon button on the Fill color toolbar. This is common knowledge. But what if you want to alter the background colour of all of the cells that have a particular value? In addition, what should you do if you want the cell's background colour to alter itself automatically whenever the value of the cell changes?

You will find the answers to these questions further on in this post, as well as learn a handful of helpful hints that will assist you in selecting the appropriate strategy for any given undertaking.

Changing the Value Based on Cell Color with Conditional Formatting in Excel

Let’s understand step by step with an example.

Step 1

In our example, we have the student’s name, and marks in an excel sheet in columnar format. Refer to the below screenshot.

Step 2

In the first step, you need to apply the conditional formatting to the range B2. After making your selection in B2, go to the Home tab, then click onditional Formatting and select New Rule. Refer to the below screenshot.

Step 3

In the New Formatting Rule dialog box, select Format only cells that contain from the section titled Select Rule Type. This will restrict formatting to only those cells that contain the specified criteria.

You can configure the rule requirements in the lower half of the dialog box, under the heading Format Only Cells with section. As you can see in the screenshot that follows, we have decided to format just the cells that have a Cell Value that is greater than 35. After that, select the desired background colour by clicking the Format option and making your selection from the drop-down menu that appears. See screenshot.

Step 4

In the Format Cells dialog box, click on the Fill tab and then choose the color you want. In our example, we choose a Light green color and click OK button. As shown in the below screenshot.

Step 5

You have been brought back to the window where you created the new formatting rule, and the Preview box has updated to reflect the format modifications you made. To confirm that everything is in working order, click the OK button. Refer to the below screenshot.

Step 6

Now, you can see the changed values based on cell color. Refer to the below screenshot.

Step 7

Because we still need to apply one more condition, which is to make the background colour of cells whose values are less than 35 a shade of red, click the New Rule button once more, and then proceed to repeat steps 2 through 6 in order to set the necessary condition. The following is a preview of our second rule for applying conditional formatting. Refer to the below screenshot.

Step 8

When you are finished, you can proceed by clicking the OK button. You should now have a table with a neat arrangement that displays the marks that are greater than and less than what you already have. Refer to the below screenshot for the same.

Conclusion

In this tutorial, we used a simple example to show how you can change the values in Excel based on cell color with conditional formatting rule.