Change background color of cells based on value

This tutorial will guide how to change the background color of cells based on specific value in excel. How to change the background color automatically when the cell value’s changes. You will learn that how to change the background color based on the certain value of cells with conditional formatting command in excel 2016, 2013 or other versions.

Change background color based on cell value

Assuming that you have a list of grade record in a range of cells in excel worksheet, and if you want to change the background color of cells based on cell values. And you also want to change the color dynamically to reflect the value changes.

You want to format the background color of cells as red based on value that is less than 60, and format color as blue if the cell value is between 60 and 80. Or greater than 80 to set the background color of cells as green.

There is a very useful excel command name as “conditional formatting” in MS excel, you can use it to format the cells based on its certain values, such as: background color or fond color changing, or others formats.

You just need to refer to the following steps:

Step 1# select the cells that you want to change the background color (you need only to select the cells that containing grade values).

change background color cell value1

Step 2# go to the Home Tab, then click Styles group-> “Conditional Formatting” command, then click on “New Rule…” menu.

change background color cell value2

Step 3# the “New Formatting Rule” window will appear.

change background color cell value3

Step 4# select “Format only cells that contain” under “Select a Rule Type:” selected box. Then you need to set the formatting rule under “Format only Cells with”, specify the conditions that you need. Such as: select “Cell Value” from the first drop down list, select “less than” from the second drop-down list and enter the specific cell value as 60.

change background color cell value4

Step 5# click the “Format…” button, then switch to the Fill Tab, select the color that you want to set, such as: red color, select OK button.

change background color cell value5

Step 6# back to the “New Formatting Rule” window, then click “OK” button. Let’s see the last result:

change background color cell value6

Then we can set the background color for other certain cell values, you just need to set the different format rules and repeat the above steps.

change background color cell value7

If you want to change the font color of cells based on value in excel, you just need to use the same ways above, and just need to switch to the Font Tab in step 5, and set the color that you want to specify.  Let’s see the below screenshot about changing font color:

change background color cell value8

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar