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.

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

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

excel change cell color of greater values

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

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

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


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.

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

formatted cells for greater values

  • Repeat the same procedure for changing color by selecting less than as the condition and referencing cell F6:
=$F$6

adding rule for lesser values

  • Here’s the result.

formatted cells for greater and lesser values

  • If the values in cells 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


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.

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. We have put 20.

  • You can also change the color.
  • Click OK.

formatted cells for greater than values

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


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.

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 to the right.

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

color changed for qbs

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

excel change cell color based on value for texts


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.

excel change cell color based on value using vba

  • 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
  • Save your code.
  • 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 colors will change depending on the value.

excel change cell color based on value


Download the Practice Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
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

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

  2. Perfect, managed to pick it up and work it out for myself after following the first couple steps. Super helpful!

    • Hello Rebecca,

      Thanks for your kind words, Rebecca! I’m glad to hear that the steps helped you figure it out. Feel free to reach out if you need further assistance or have more questions. Happy Excel-ing!

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo