How to Change Cell Color Based on a Value in Excel (5 Ways)

Method 1 – Dynamically Change Cell Color Based on a Value

Consider the following dataset that shows U.S. state populations. We’ll divide the population numbers into 3 categories: above 20 million, below 5 million, and in between.

Steps:

Select the range of cells you want to format.

Select Conditional Formatting under Home.
Select New Rule from the drop-down list.

In the New Formatting Rule box, select Format only cells that contain under Select a Rule Type.
In the Rule Description, choose the condition greater than or equal to and put in the value 20.

Click on Format.
Go to the Fill tab in the Format Cells box and pick a background color. We chose red for the example.

Click on OK on both Format Cells and New Formatting Rule.

Repeat the process and put between as condition and 5 and 20 as values.

Repeat for lower than or equal to 5 and you will have your cell color changed according to the values for the full range.

Method 2 – Change Cell Color Based on a Value of Another Cell

We have selected two values in cells F5 and F6 as a source to customize from.

Steps:

Select the range of cells you want to format.

Select Conditional Formatting and choose New Rule.

In the New Formatting Rule box, select Format only cells that contain under Select a Rule Type.
In Rule Description, choose the condition to be greater than and put the following formula:

=$F$5

Click on Format.
In the Fill tab, select a background color.

Click on OK on both Format Cells and the New Formatting Rule.

Repeat the same procedure for changing color by selecting less than as the condition and referencing cell F6:

=$F$6

Here’s the result.

If the values in cells F5 or F6 change, the colors from the range of cells C5:C16 will change accordingly.

Method 3 – Using the Quick Formatting Option to Change Cell Color in Excel

Steps:

Select the cell and hover over the bottom-right corner of the selected range. A Quick Analysis Toolbar Icon will appear.

Click on it.
In the Formatting tab, select Greater Than.

In the Greater Than tab, select the value above which the cells within the range will change color. We have put 20.

You can also change the color.
Click OK.

You can also select the Color Scale option in the Formatting tab from the Quick Access Toolbar Icon to have a different range of colors for the column.

You will have a wide range of color cells based on the percentiles: red for the lowest, to white, to green for the highest.

Method 4 – Change Cell Color Permanently Based on a Value

We have three values as positions and will show you how to have three different colors for QB, LB, and WR.

Steps:

Select the range of cells you want to modify.

In the Home tab, select Find & Select from the Editing section.
From the drop-down list, select Replace.

In the Find and Replace box, put QB in the Find what box.
Put QB in the Replace with box and change the format to the right.

Select Replace All and you will have all the boxes with QB as the value will change to this color.

You can keep changing colors for cells with different values by replacing the text and formatting, then pressing Replace All.
After changing colors for all three values, close the box.

Method 5 – Change Cell Color Based on a Value Using Excel VBA

You may need to enable the Developer tab if you don’t see it on your ribbon.

Steps:

Select your cells and go to the Formulas tab.
Select Define Name under the Defined Names group.

Name your range in the Edit Name. We will be using “Positions”. Name it the same if you want to copy the VBA code.

Click on OK.
Go to the Developer tab and select Visual Basic.

In the VBA window, select Insert and choose Module.

Insert the following code into the module:

Sub Change_Cell_Color()
Dim cell_value As Range
Dim stat_value As String
Dim rng As Range
Set rng = Range("Position")
For Each cell_value In rng
stat_value = cell_value.Value
Select Case stat_value
    Case "QB"
    cell_value.Interior.Color = RGB(0, 255, 0)
    Case "WR"
    cell_value.Interior.Color = RGB(255, 255, 0)
    Case "LB"
    cell_value.Interior.Color = RGB(255, 0, 0)
End Select
Next cell_value
End Sub