Two ways to change background color in Excel based on cell value

Find and select all cells that meet a certain condition

There may be several possible scenarios depending on what kind of values you are looking for.

If you need to color cells with a particular value, e.g. 50, 100 or 3.4, go to the Home tab, Editing group, and click Find Select > Find….

Enter the needed values and click the Find All button.

In our previous example, if we needed to find all gas prices between 3.7 and 3.799, we would specify the following search criteria:

Now select any of the found items in the lower part of the Find and Replace dialog window by clicking on it and then press Ctrl + A to select all found entries. After that click the Close button.

This is how you select all cells with a certain value(s) using the Find All function in Excel.

However, what we actually need is to find all gas prices higher than 3.7 and regrettably Excel's Find and Replace dialog does not allow for such things.

Luckily, there is another tool that can handle such complex conditions. The Select Special Cells add-in lets you find all values in a specified range, e.g. between -1 and 45, get the maximum / minimum value in a column, row or range, find cells by font color, fill color and much more.

You click the Select by Value button on the ribbon and then specify your search criteria on the add-in's pane, in our example we are looking for values greater than 3.7. Click the Select button and in a second you will have a result like this:

If you are interested to try the Select Special Cells add-in, you can download an evaluation version here.

Change the background color of selected cells using "Format Cells" dialog

Now that all cells with a specified value or values are selected (either by using Excel's Find and Replace or Select Special Cells add-in) what is left for you to do is force the background color of selected cells to change when a value changes.

Open the Format Cells dialog by pressing Ctrl + 1 (you can also right click any of selected cells and choose "Format Cells…" from the pop-up menu, or go to Home tab > Cells group > Format > Format Cells…) and make all format changes you want. We will choose to change the background color in orange this time, just for a change :)

If you want to alter the background color only without any other format changes, then you can simply click the Fill color button and choose the color to your liking.

Here is the result of our format changes in Excel:

Unlike the previous technique with conditional formatting, the background color set in this way will never change again without your notice, no matter how the values change.

Use Excel formula to change background color of special cells

A cell's color will change automatically based on the cell's value.

This method provides a solution that you will most likely need in 99% of cases, i.e. the background color of cells will change according to the conditions you set.

We are going to use the gas prices table again as an example, but this time a couple of more states are included and some cells are empty. See how you can detect those blank cells and change their background color.

1. On the Home tab, in the Styles group, click Conditional Formatting > New Rule… (see step 2 of How to dynamically change a cell color based on value for step-by-step guidance).
2. In the "New Formatting Rule" dialog, select the option "Use a formula to determine which cells to format". Then enter one of the following formulas in the "Format values where this formula is true" field:
   • =IsBlank()- to change the background color of blank cells.
   • =IsError() - to change the background color of cells with formulas that return errors.

   Since we are interested in changing the color of empty cells, enter the formula =IsBlank(), then place the cursor between parentheses and click the Collapse Dialog button in the right-hand part of the window to select a range of cells, or you can type the range manually, e.g. =IsBlank(B2:H12).

3. Click the Format… button and choose the needed background color on the Fill tab (for detailed instructions, see step 5 of "How to dynamically change a cell color based on value") and then click OK.

   The preview of your conditional formatting rule will look similar to this:

4. If you are happy with the color, click the OK button and you'll see the changes immediately applied to your table.

Change the background color of special cells statically

Once changed, the background color will remain the same, regardless of the cell values' changes.

If you want to change the color of blank cells or cells with formula errors permanently, follow this way.

1. Select your table or a range and press F5 to open the "Go To" dialog, and then click the "Special…" button.
2. In the "Go to Special" dialog box, check the Blanks radio button to select all empty cells.

   If you want to highlight cells containing formulas with errors, choose Formulas > Errors. As you can see in the screenshot above, a handful of other options are available to you.

3. And finally, change the background of selected cells, or make any other format customizations using the "Format Cells" dialog as described in Changing the background of selected cells.

Just remember that formatting changes made in this way will persist even if your blank cells get filled with data or formula errors are corrected. Of course, it's hard to imagine off the top of the head why someone may want to have it this way, may be just for historical purposes :)