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.

excel change cell color based on value

  • 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.

excel change cell color based on value

  • 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.

excel change cell color based on value

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

excel change cell color based on value

  • 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.

excel change cell color based on value

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

Read More: Excel Formula Based on Cell Color (5 Examples)


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.

excel change cell color based on value

  • 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:
=$F$5

  • 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.

excel change cell color based on value

  • 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.
=$F$6

excel change cell color based on value

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

excel change cell color based on value

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: How to Fill Color in Excel Cell Using Formula (5 Easy Ways)


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.

excel change cell color based on value

  • 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.

excel change cell color based on value

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.

excel change cell color based on value

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


Similar Readings:


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.

excel change cell color based on value

  • 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.

excel change cell color based on value

  • 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.

excel change cell color based on value

Related Content: How to Highlight Cells in Excel Based on Value (9 Methods)


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.

excel change cell color based on value

  • 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.

excel change cell color based on value

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

excel change cell color based on value

Read More: VBA to Change Cell Color Based on Value in Excel (3 Easy 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

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo