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

Related Posts
How to Sort Data but Keep Blank Cells in Excel

In daily work, if we sort data with blank cells included in the same column, these blank cells are listed at the bottom automatically after sorting. If we want to keep the positions of these blank cells unchanged and only ...

How to Copy and Paste Only Values and Ignore Formula

When we copy a cell applied with a formula, we copy the formula of the cell rather than copy the value showing in the cell. In this article we will introduce you the way to copy only value ignoring applied ...

How to Sort Date by Day of Week in Excel

Except sort data by “A to Z” (alphabetical order, for numbers from small to large), we can also sort data by date, month or year if these conditions are given. In this article, we will show you the way to ...

How to Select All Non-Blank Cells of a Range

In daily work, we may meet the cases that select all blank cells or non-blank cells of a range. You may know the way to select all blank cells as they are “blanks”. But for non-blank cells, they may contain ...

How to Highlight Cells Based on Their First Letter/Character by Conditional Format in Excel
Highlight Cells Based on Their First Letter 11

Sometimes we want to highlight some cells based on certain criteria, for example highlight all cells which the first letter is X, this requirement is quite commonly used in searching. Actually, to implement this function, you can highlight these cells ...

How to Remove Conditional Formatting on Blank Cells in Excel
remove conditial formatting on blank cell6

This post will guide you how to remove conditional formatting if cell is blank in Excel. How do I skip conditional formatting for blank cells in Excel 2013/2016. Remove Conditional Formatting on Blank Cells Assuming that you have a list ...

Conditional Formatting Rows by Groups in Excel
conditional format rows by group8

This post will guide you how to apply conditional formatting for alternate row color based on groups in your worksheet in Excel. How do I alter row color by groups using conditioanl formatting in Excel 2013/2016. Alternating Rows Color by ...

How to Highlight Cell or Row If Date Is In Current Day/Week/Month in Excel
highlight cell or row if date8

This post will guide you how to highlight cell if date is the current day or is in the current week or month in Excel. How do I highlight row if date is in current week or month with conditional ...

How to Highlight Cells Greater Than a Certain Length in Excel
highlight cells based on cell lenght6

This post will guide you how to highlight a cell that contains more than a certain length using Conditional Formatting function in Excel. How do I highlight cells greater than or less than a certain text length in Excel. Highlight ...

How to Highlight Dates Older Than 30 Days in Excel
highlight dates older than 30 days5

This post will guide you how to highlight dates older than 30 days in Excel. How do I use conditional formatting to highlight if date is greater than 30 days old in Excel. How to highlight a row or cell ...

Sidebar