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

Get FREE Advanced Excel Exercises with Solutions!

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.


Change Cell Color Based on Value in Excel: 5 Ways

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 using conditional formatting

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

formatting cells to change cell color based on value in excel

  • 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 of greater values

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

setting new rule to change cell color based on value in excel

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


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.

selecting conditional formatting to change cell color based on another cell's 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 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.

formatted cells for greater values

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

adding rule for lesser values

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

formatted cells for greater and lesser values

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

quick formatting options to change cell color based on value in excel

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

formatted cells for greater than values

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 using color scale


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 section.
  • Then, from the drop-down list, select Replace.

excel change cell color permanently based on value

  • In the Find and Replace box, put QB in the Find what box.
  • 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.

color changed for qbs

  • 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 for texts


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 to enable 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 using vba

  • 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 Macro box, select the code you have just created and run.

selecting macro name

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

excel change cell color based on value


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.


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.


Related Articles


<< Go Back to Color Cell in ExcelExcel Cell FormatLearn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Abrar-ur-Rahman Niloy, holding a B.Sc. in Naval Architecture and Marine Engineering, has contributed to Exceldemy for nearly 1.5 years. As a leader in Excel, VBA, and Content Development teams, he authored 114+ articles and assisted the Exceldemy forum. Presently, as a project writer, he prioritizes stepping out of his comfort zone, aiming for constant technical improvement. Niloy's interests encompass Excel & VBA, Pivot Table, Power Query, Python, Data Analysis, and Machine Learning libraries, showcasing his commitment to diverse... Read Full Bio

2 Comments
  1. Thanks so much, it worked perfectly! I used the 1st method on my old Office 2010 perpetual.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo