Go Back

How to Use Conditional Formatting to Change Cell Background Color Based on Cell Value

Read time: 28 minutes

Changing the color of a cell might seem simple enough, but what if you wanted cells to be a particular color based on certain criteria? In these cases, you would use conditional formatting to change your cell’s background color.

Basic cell color formatting

Did you know that you can change the background color of cells by going into the formatting of the cell? Then, you can choose the Fill section and select the intended color to fill the cell.

In the above example, the color of cell E3 has been changed from No Fill to Blue color, and notice that the value in cell E3 is 6 and if you change the value in this cell from 6 to any other value the cell color will not change and it will always remain blue.

What does this mean?

This means that cell color is independent of cell value, so no matter what value is entered in E3, the cell color will always be blue. You can refer to this as static formatting of the cell E3.

Cell color formatting based on the cell’s value

Now, what if you want to change the cell color based on cell value? Suppose, you want that color of cell E3 changes with the change of the value in it. Say, you want to color of cell E3 based on values as following.

0 -10: You want the cell color to be Blue

11-20: You want the cell color to be Red

21-30: You want the cell color to be Yellow

Any other value or Blank: No color or No Fill.

You can achieve this with the help of conditional formatting. On the Home tab, in the style section group, click on Conditional Formatting —–> New Rule

Note: Make sure the cell on which you want to apply conditional formatting is selected.

Then select “Format only cells that contain,” then in the first drop down select “Cell Value” and in the second drop-down select “Between.”

Then, on the first box enter 0 and in the second box enter 10, then click on the Format button and go to Fill Tab, select the blue color, click ok and again click ok.

Now enter the value between 0 and 10 in cell E3, and you will see that cell color changes to blue and if there is any other value or no value then cell color changes to blank.

Repeat the same process for 11-20 and 21-30, and you’ll see that number changes as per the value of the cell.

In the example above, you are changing the cell color based on that cell value only. You can also change the cell color based on other cells value as well. Suppose you want to change the color of cell E3 based on the value in D3. To do that, you have to use the formula in conditional formatting.

Now suppose if you want to change the color of cell E3 to blue if the value of cell D3 is greater than 3, and to green, if the value in D3 is greater than 5, and to red if the value is greater than 10.

Again follow the same procedure.

On the Home tab, in the style section group, click on Conditional Formatting —-> New Rule

Note: Make sure the cell on which you want to apply conditional formatting is selected

Now select Use a formula to determine which cells to format, and in the box use the formula, D3>5, then select the formatting to fill the cell color to green.

Now notice here that we have selected the cell E3 and not D3, because we want to change the color of cell E3 based on the value of D3. Also, see the formula “=D3>5”, if D3 is greater than 5 then the value of E3 will change to green. Click ok and see the color of cell E3 changes to green as D3 right now contains 6.

Now let’s apply the conditional formatting to E3 if D3 is greater than 3. This means if D3>3 then cell color should become “Blue” and if D3>5 then cell color should remain green as we did it in the previous step.

Now if you follow the above steps as you did for Green color, you will see that even if the cell value is 6, it is still showing blue color and not green. It is because it takes the latest conditional formatting you set for that cell, and as 6 is also greater than 3 hence it is showing blue color, but it should show the green color.

So, you have to arrange the rules you have applied for any particular cells. You can do that by going into the Manage Rules option of conditional formatting.

You can see all the rules applied on that cell and there you can arrange the rules or set their priority by using the arrow buttons. A number greater than 5 will also be greater than 3. Hence the rule “greater than 5” will take priority and you have to move it upwards by using the up-arrow button.

Now when you enter 6 in D3, the cell color of E3 will become green and when you enter 4, the cell color will become blue.

Still need some help with Conditional Formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Basically..... or not so basically as it turns out, I would like create a progress bar in Excel to show the progression towards hitting an incentive sales credit award that will change color based on the % completed towards goal. There are two thresholds: 1. At 80% of goal, Employees will receive the BASIC award payout (everything below 80% receives no award) 2. At 110% and above of goal, Employees will receive the BONUS award payout I want the progress bar to follow the below color scheme format: <80% = Red Bar >=80%, <110% = Green Bar >=110% = Blue Bar I want this to be one single bar that changes color based on the % values in another cell The bar itself would chart progress from 0% to a maximum of 110% (since there will need to be a cap in order to show progress and I assume if the cell value is 150% the progress bar would be fully filled in?) If the Employee is at 60% of goal, the bar should be RED If the Employee is at 90% of goal the bar should be GREEN (Changes from Red to Green at 80%) If the Employee is at 125% of goal the bar should be BLUE (Changes from Green to Blue at 110%) Is there some way to make this possible? even if it involves VBA? Thank you so much! I have tried multiple work-arounds but nothing yields results close enough to what I am looking for.
Solved by K. J. in 20 mins
Hi, I have a google sheet shared by the team, I set the conditional formatting, the row will turn into certain colors as cell values are updated, the next thing I want to accomplish is to move all the rows that has changed to certain colors to another spreadsheet, automatically...Is it something achievable?
Solved by X. J. in 22 mins
How do I make cells change color if they have matching values and also, how do I make them change color based on the value of another cell?
Solved by D. D. in 21 mins
I am in need of a data analysis formula where, 1) if I change the color of text in a column it takes the cell value and adds a 1 continuously to another cell. for example if multiple cell values are 1.6 in T:T(let say 3/5 total) and I change the correct 3 to red font, it then would add 1 for each red font in that column to cell M6. If it was incorrect i leave it automatic font and it would not add a 1.
Solved by S. H. in 14 mins
i made a conditional formatting rule for one cell to change color if the value entered is the same as another cell. all cells where the origional cell is 0, the other cell has already changed color. how do I fix it?
Solved by D. B. in 22 mins
I need to pout conditional formatting for a scenario where if the cell value is positive then green and red if the cell value is negative, however, i also need to give refernce to other cell where another value basis which i need to validate before giving the cell a red color
Solved by O. U. in 17 mins
I'm trying to use conditional formatting to color code cells that are greater or less than another cell. However there is text in the cells and the conditional formatting isn't correctly color coding values that are higher or lower.
Solved by Z. F. in 28 mins
Is there a formula to change the text (bold / color) of a cell, if the value in another cell is over a certain value? Kind of like an if-then statement in programming? On another note, is there a way to freeze the number in a cell that already has a formula in it, if another cell is over a certain number?
Solved by S. Y. in 20 mins
Need help with Formulas. I want my formula to be matching the amount of colors in one cell with another value in a cell.
Solved by Z. S. in 15 mins
Please can you help me. I would like to format the background of the cells in Column L if the value in another cell on the same row (column C) is blank. I have this code which formats the cells in Column C which is the cell that I am checking to see if it is blank, but how do I change it so that it formats column C instead? On Error Resume Next Set CheckRange = Sheets("Quote").Range("C2") Set CheckRange = Range(CheckRange, Cells(ActiveSheet.Rows.Count, _ CheckRange.Column).End(xlUp)) For Each Cell In CheckRange.Cells If Cell.Value <> "" Then Cell.Interior.ColorIndex = 0 End If Next Cell On Error GoTo 0
Solved by I. E. in 23 mins
There are 3 existing sheets: sheet 1, sheet 2, sheet 3. They function as follows: Sheet 1: Is the most current full list of articles and prices which CAN NOT be edited. Sheet 2: Has a list of updated prices which gotta check if they are current compared to list 1. Sheet 3: Is the output sheet. It's an exact copy of sheet 1, but when a price from sheet 2 varies from the price of sheet 1, the price in sheet 3 gets updated with the price of sheet 2. My FOR loop does not function as I desire. I want the code to: compare an entire row from sheet 1 with another entire row on sheet 2. When certain values in the row vary, they have to change the Interior.ColorIndex of the cell that varies and paste the values of the cell that varies in sheet 2 into sheet 3.
Solved by K. B. in 12 mins

11
Leave a Comment

avatar
11 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
9 Comment authors
JamilCarlDanStephCarol Recent comment authors
newest oldest
Alex
Guest
Alex

Hi there im just wondering if it would be possible to create a conditional formatting that allows you to click on a point in a scatter plot and have it highlight a row/cell of data?

Amir
Guest
Amir

i need to run conditional formatting on a cell, but i need the type of conditional formatting to depend on the date in another cell, is this possible?

Alex
Guest
Alex

I am struggling to create a conditional formatting formula that is dependent on the contents of another cell. which changes color depending on the proximity of the contents of the second cell

Daniel
Guest
Daniel

I’m trying to use conditional formatting to highlight cells with time values between (or including) 0:00 and a value in another cell, but this is not working.

Dan
Guest
Dan

I would like to indicate at the top of a column with an icon, if there is conditional formatting in any of the cells below in that column. Further, can you change the icon based on the colour of the conditionally formatted cells?

Annie
Guest
Annie

Thanks for the article! I need help with some conditional formatting and some logic formulas dealing with dates. Should be super easy.

Carol
Guest
Carol

i need help regarding conditional formatting. i want to format a cell based on another cells value. example is given on attached sheet

Steph
Guest
Steph

I am trying to add a conditional formatting to an excel sheet based on percentages. How would I do that?

Dan
Guest
Dan

my 30:00 keeps auto correcting to 6:00 or 30:00:00 I would use text, however I have a conditional formatting set for that cell

Carl
Guest
Carl

How can I multiply the number of blank cells in a row by 5 using conditional formatting?

Jamil
Guest
Jamil

I am experiencing a new glitch in excel where using conditional formatting the cells that are blank are registering as a 0 and will highlight, even if I include a format that says all blank cells have no formatting it still doesn’t change. I have used the same conditional formatting previously and it does not do this.