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

While performing data analysis, you may need to find out particular values favorable to your needs. Highlighting them by changing cell color is one way to find this out from a big pool of data. In this article, I am going to show you how you can change cell color based on the value in Excel.

Download Practice Workbook

You can download the notebook used to demonstrate in this article with the examples included both with and without the colors changed from below.

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

Here, I will be demonstrating 5 methods you can use to change cell color for particular conditions. For an easily digestible demonstration, I am going to use two different datasets- one with numerical values to classify them into ranges and one with text values.

1. Dynamically Change Cell Color Based on a Value

With this method, your cell color won’t be permanent. The color of the cell will change according to the rule you have set if you change the value in it. For this, I will be using conditional formatting.

To demonstrate the procedure, I will be using the following dataset.

I am going to divide the population numbers into 3 categories- above 20 million, below 5 million, and one in between.

Steps:

• Select the range of cells you want to format.

• Then select Conditional Formatting under the 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.

• Now, click on the Format to fill in the cell with colors. For this, go to the Fill tab in the Format Cells box and pick a background color. I am selecting red for this example.

• After that, click on OK on both the Format Cells and New Format Rule . The cells with values higher than 20 will turn red now.

• You can follow the same procedure from the start and put between as condition and 5 and 20 as values.

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

If you use this method and change the value of a red box to something under 5, it will change to green.

Read More: How to Highlight Cells in Excel Based on Value (9 Methods)

2. Change Cell Color Based on a Value of Another Cell

Let’s say, we want to change the colors of cells based on the value from another cell. If the value of that particular cell changes, the color of the main range will change accordingly.

For this, I 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.

• Then select Conditional Formatting under the Home tab.  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 to be greater than or equal to and put the following:

• Click on Format and in the Fill tab, select the background color.

• Click on OK on both Format Cells and the New Formula Rule. You will have your cells’ color changed.

• Repeat the same procedure for changing color for values less than 5 by referencing it from cell F6 by selecting less than as the condition and the following as the value in the New Formatting Rule box.

You will have your whole range’s color changed based on the value of reference cells.

Now, if the values in either cell F5 or F6 change the colors from the range of cells C5:C16 will change accordingly.

Read More: VBA to Change Cell Color Based on Value in Excel (3 Easy Examples)

3. Using Quick Formatting Option to Change Cell Color in Excel

For quicker, but less flexible formatting options, you can use quick formatting to change cell color based on value. Just follow these steps.

Steps:

• Select the cell and hover your mouse cursor in the lower portion 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. I have put it 20 here.

• You can also change the color. After that, click OK.

After that, you will have your cell colors changed which will be above 20.

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.

Read More: Excel Cell Color: Add, Edit, Use & Remove

Similar Readings

• How to Highlight Every 5 Rows in Excel (4 Methods)
• How to Highlight from Top to Bottom in Excel (5 Methods)
• Highlight Partial Text in Excel Cell (9 Methods)
• How to Highlight a Row in Excel (5 Quick Methods)
• How to Highlight a Column in Excel (3 Methods)

4. Change Cell Color Permanently Based on a Value

There is also another method you can use to change colors in an Excel column by using Find and Replace. But this color change will not be dynamic and the color will remain the same if you change the value within the cell until you change it manually again.

For demonstration, I have selected the following dataset with recurring text values for easier demonstration. You can also do the same for numerical values.

Here, in this example, I have three values as positions and I 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
• Then, from the drop-down list, select Replace.

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

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

• Without closing the Find and Replace box you can keep changing colors for cells with different values.
• After changing colors for all three values, close the box. You will have your cells formatted in the range.

5. Change Cell Color Based on a Value Using Excel VBA

You can use Microsoft Visual Basic for Applications(VBA), for intermediate and advanced users. First of all, you need the Developer tab to change cell color based on value.

If you have the Developer tab in your ribbon, you can follow these easy steps.

Steps:

• First, name your range. To do that select your cells and go to the Formulas tab, and select Define Name under the Defined Names group.

• Name your range in the Edit Name I will be using “Positions” here. I suggest you name it the same if you want to copy the VBA code.

• Click on OK.
• Go to the Developers tab and select Visual Basic from there.

• In the VBA window select Insert, then Module.

• Now, in this new module, type the following code.

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

• Save your code.
• Now, go to Macros under the Developers tab.

• In the Macros box, select the code you have just created and run.

Your cell color will now change depending on the value you have in that range.

Read More: Excel VBA to Highlight Cell Based on Value (5 Examples)

Conclusion

These were the different methods you can use to change cell color based on value in Excel. Hope this guide was informative and helpful for you. If you have any questions or recommendations, feel free to let us know down below.

For more guides like this, explore Exceldemy.com.

Related Articles

• How to Highlight Selected Cells in Excel (5 Easy Ways)
• How to Highlight Selected Text in Excel (8 Ways)
• Highlight Cells That Contain Text from a List in Excel (7 Easy Ways)
• How to Compare Two Excel Sheets and Highlight Differences (7 Ways)
• Highlight Cell Using the If Statement in Excel (7 Ways)
• How to Highlight Text in Text Box in Excel (3 Handy Ways)