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.
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.
- 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
- Save your code.
- Go to Macros under the Developers tab.
- In the Macro box, select the code you have just created and run.
- Your cell colors will change depending on the value.
Download the Practice Workbook
Related Articles
- How to Color Code Cells in Excel
- Excel Formula to Change Cell Color Based on Text
- How to Fill Color in Cell Using Formula in Excel
- How to Fill Cell with Color Based on Percentage in Excel
- Excel Formula to Color Cell If It Has Specific Value
- How to Change Text Color with Formula in Excel
- How to Apply Formula Based on Cell Color in Excel
<< Go Back to Color Cell in Excel | Excel Cell Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Thanks so much, it worked perfectly! I used the 1st method on my old Office 2010 perpetual.
Hello Gallienus,
You are most welcome.
Regards
ExcelDemy
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