Comments on: Two ways to change background color in Excel based on cell value

In this article, you will find two quick ways to change the background color of cells based on value in Excel 2016, 2013, and 2010. Also, you will learn how to use Excel formulas to change the color of blank cells or cells with formula errors. Continue reading →

Comments page 2. Total comments: 426

  1. Hi, Thanks for your guidelines, very helpful.
    I have changed colours of cells as I wanted to. However do you know how I can then make the values disappear and only have the colours come up on cells?

    1. Hi,

      If I have a number in collum a, let's says 100, and whatever the number is in collumn b id like to change the colour of based on the following:
      If 30 % or less than collumn a number turn it red
      If 30 to 60 percent of column a turn another colour aka orange
      If 60 or more percent turn another colour aka green

      So if colum b value is
      20 = red
      50 = orange
      605= green

      Many thanks!

      1. Hi Lucky, Chose same color for Fill & Font in your conditional formatting settings. That will help you showing only colors based on value, no text.

  2. Hi, how can i change the fill color by using formula for the below

    IF my Cell no F16 is X i want to fill entire row 16 as one specific color

    can any one help

  3. Hi, thank you for the instruction.

    My problem is, can it change color if it less/more than the month before.
    for example,
    if C2 is higher than B2 then cell C2 is red, if lower then C2 is green

    if D2 is higher than C2 then D2 is red and so on.

    1. Hi!
      Please re-check the article above since it covers your task.
      Follow the instructions in the first paragraph of this manual. Specify Cell Value - greater than - B2. Or use the formula =C2>B2

  4. How is it possible i complete the entry in a cell and this row automatically colored

  5. Hi, I did conditional format for the cell and i could find changes in my system.

    When it opened in some other system, these changes are not refelecting even though formula available for the said cell,

  6. Hi,

    Can we highlight a cell based on cursor position without using vba, because i need it on spreadsheet.
    (if I move or select a particular cell, that cell will get changed to color or highlighted). Can anyone help me for this issue,

    Thank you,

    1. Hi!
      Conditional formatting works with formulas. But there is no formula that determines the position of the cursor.

  7. I have set the rule to change the color of the cell. It's working fine. However, by the same rule, is it possible for me to change the font color as well?

    1. Hello!
      You can do this when creating or editing a conditional formatting rule. In the Format Cells dialog box, switch to the Font tab and select the color of your choice.

  8. Hi there,
    my problem is a bit different. I want to highlight updated cells in a pre filled data. E.g. I already have a set of data, but due to some reason any one or few cells with existing data may get modified (by someone else in my team), I want to apply conditional formatting to my sheet so that those updated cells get highlighted by its own. So that, next time when I look into my data, it will show me the changes.

  9. I want to turn a cell into Green when I type 1 and red when I type 0. Also, I want the entered number to disappear once the cell turns Green/Red

      1. This did not help me. I'll be very glad if someone understands what I'm trying and comes up the solution to this.

  10. I tried following your instructions for changing cell colour depending upon its value (in my case it is text), but it does not work??

    1. Did you find a solution to this? I'm wanting to populate cells with text and change the colour.

      1. Hi!
        Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.

  11. How i can using condition formating if we need notif colour red if im done working 6 day. Every working 6 day automatically red colour

  12. Thank you!!!!!!!!!!!!!!!!!!!!
    Simple and practical with excellent explanation

  13. On an existing spreadsheet of dollar amounts, I want to use conditional formatting to show the cell's value is greater than the value of the cell above it by setting it to light green. I followed your example and it worked fine for that cell. But how do a do a mass change of all the cells in that column (except the topmost cell, of course)? Same question: how do I make that change for two dozen columns? Basically I want: "Set this cell' background to light green if its value is greater than the value in the cell just above it." - i.e., same column, but row minus 1.

    Thanks,

  14. thank you for this useful information

  15. Hi,

    What if you wanted the colour to change where the condition is compared to another cell? I want a cell to turn red when the value is less than the other cell, or green if the value is higher.

  16. I have the standard excel, I want, if I can any field in that standard excel there should be color change, I am not able to find it, pl sugest.

  17. My question is how do I highlight groups of rows with different colors based on the duplicate value.
    For example: all the rows with 2004 in column G would be any color, then all the rows with 2005 in column G be any other different color. There are years from 1991 to 2020 in 500 row table.

  18. thank you so much bhai

  19. This is Resolved. Thank you

  20. Sorry, Making it a little bit complicate, if there is anything other than "Pending" or "Complete" in Column A[n] then the corresponding column in B[n] should be Yellow.

    Please suggest? Many thanks.

  21. Its done. Thank you.

  22. What if the conditional formatting needs to be based on text and not numbers.

    Example: Turn column B1 Red, if the Value Column A1 entered as “pending”. Turn column B2 Green, if the Value Column A2 contain the is entered as “complete”.

    Appreciate your help and thanks in advance

    1. Hello!
      Use two conditional formatting formulas:

      =A1="pending"
      =A2="complete"

      Create a conditional format for each color with these formulas.
      I hope my advice will help you solve your task.

      1. Thanks for the immediate response. That's really great.

        I got it done individually. But I got multiple values in column A. A1 might be having "Pending" A2 might be having "Complete" similarly till A2000. How to apply this to the whole column. Based on the value in Column A[n] the colour coding should be applied in Column B[n]. Whenever there is a change in Column A from "Pending" to "Complete" or vice-versa, that should change the color in Column B[n] accordingly .

        Please suggest?

        1. Hello!
          Please reread the article above, it covers your case completely. Select the table or range where you want to change the background color of cells. After that, create a conditional formatting rule. Thank you.

  23. Thank you for this example. I was able to complete the task that I wanted!

  24. Thanks for the help...It's like a magic trick. I learned something new and useful today.

  25. I use conditional cell coloring a lot, especially in large spreadsheets. Please tell me if it is possible to make a cell blink, or change intermittently between two colors, so as to emphasize that this cell requires some data input?

    1. Hello Tim!
      Unfortunately, standard cell formats in Excel do not provide such effects. This can be done using the VBA macro.

  26. thx so much

  27. Very helpful post.Thanks

  28. I want that a cell is to automatically get changed its background color when the value is replaced of that particular cell. So that I can get information that this data file has some chamges made by my colleagueue. (like option of Track changes in MS Word)

  29. Hi,

    Can't find anything on web to help me out with my problem so maybe you can give a hand on this one:
    So, I want to create a drop down list with colors (cells with colors so I can chose the color from a list) but I cannot find how to do it. Then I have another one related to the previous which is I want to do a drop down list based on another one but then have the result color based on the text selected from the drop down menu if that makes sense :D... would be very appreciated if someone can help me with it... cheers

    1. Hello Bruno!
      The contents of the drop-down list cannot be colored. The drop-down list uses values, and the color cannot be the value of the cell. However, using conditional formatting, you can paint over a cell after a value is selected from the drop-down list.

  30. Hello! Can I use conditional formatting to highlight all cells in a range that have been changed since X date (date as specified cell A1). As an example, a cell might have $10 one week and be changed to $5 the next week. If this has happened I want to see it highlighted with a red square around it. Note it is a huge table that has an infinite amount of rows and the cells already have other conditional formatting applied. Thanks

  31. Hai,
    I just want to highlight the cell if the previous 3 cells in a row contains any numerical value.
    Can anyone please help me out.

    1. hello Arjun!
      Please try the following formula conditional formatting:

      =(ISNUMBER(INDIRECT(ADDRESS(ROW(A1),COLUMN(A1)-3,4))) +ISNUMBER(INDIRECT(ADDRESS(ROW(A1),COLUMN(A1)-2,4))) +ISNUMBER(INDIRECT(ADDRESS(ROW(A1),COLUMN(A1)-1,4))))>0

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  32. THANKS

  33. Very Very Thanks

  34. Excellent article. Thanks so much. I found exactly what I needed.

  35. It is really useful. Thank you for this good job.

  36. I looking almost the same but i need to check data value of day of weeks like sunday and saturday and the row from A1 to A8 for example to be colored automatic if they see on A1 Sunday i make calendar for my workstation

    1. 1. Select A1:A8
      2. Click Conditional Formatting
      3. Select New Rule on the pull-down list
      4. In a New Formatting Rule window that pops up, select Use formula...
      5. In the Edit the Rule Description, Format values... box enter: =(A$1="Sunday")
      6. Click Format button
      7. Select Fill tab, if you want to change the colour of the background, or Font tab, Color box, to change the colour of the font/foreground
      8. Select the colour and OK your way out
      If you want to play with the current day of the week, you can modify the formula to: =(WEEKDAY(TODAY(),1)=1)

  37. Thanks

  38. Hi,
    I am trying to use the formatting, but no luck so far.
    I need to have the whole row to change the colour based on the selection made in one of the columns (status).
    Any ideas how this should be set up?
    Thanks

    1. Pls., be exact, Aga, so I can give you the exact answer. For as much as you have specified, the answer would be something along these lines:
      1. Select the whole row you want to apply the formatting to (by clicking on the row number to the left of the column A)
      2. Click Conditional Formatting
      3. Select New Rule on the pull-down list
      4. In a New Formatting Rule window that pops up, select Use formula...
      5. In the Edit the Rule Description, Format values... box enter: =(cell_ref=content), where your cell_ref has to be absolute ($A$1 style) or mixed with fixed column (A$1 style), and content is a numeric or textual value
      6. Click Format button
      7. Select Fill tab, if you want to change the colour of the background, or Font tab, Color box, to change the colour of the font/foreground
      8. Select the colour and OK your way out

      1. 1. Select A1:A8
        2. Click Conditional Formatting
        3. Select New Rule on the pull-down list
        4. In a New Formatting Rule window that pops up, select Use formula...
        5. In the Edit the Rule Description, Format values... box enter: =(A$1="Sunday")
        6. Click Format button
        7. Select Fill tab, if you want to change the colour of the background, or Font tab, Color box, to change the colour of the font/foreground
        8. Select the colour and OK your way out
        If you want to play with the current day of the week, you can modify the formula to: =(WEEKDAY(TODAY(),1)=1)

        1. Sorry, this was the reply for TeamMas7eR

  39. I have a data set that has a distribution very much centered around a static median. If I use the color scale conditional formatting provided I get a data set that is very much yellow with the occasional red and green.

    What I need is a conditional formatting where all values above the median have a red color scale and and all values below the median have a green color scale. The values on either side of the median are very much different colors and the intensity of the color is dependent on the value.

    Is there an easier way to do this than writing a dozen or more conditional rules?

  40. How would I change cell A3 to reflect what is said in Cell M3?

    I'm creating a tracking sheet and I want cell A3 to be blank and only color coded either red or green when cell M3 says Yes(green) No(red). How would I do this?

    1. If your M3 contains textual values "Yes" or "No":
      1. Select A3
      2. Click Conditional Formatting
      3. Select New Rule on the pull-down list
      4. In a New Formatting Rule window that pops up, select Use formula...
      5. In the Edit the Rule Description, Format values... box enter: =(M3="Yes")
      6. Click Format button
      7. Select Fill tab
      8. In the lowest row of the coloured squares, select the 6th from the left (Green), and click OK
      9. Click OK to accept this rule
      10. Repeat steps 2. - 9. except:
      10.5. In the step 5., the formula is: =(M3="No")
      10.8. In the step 8., select the 2nd box from the left (Red)
      11. Click Conditional Formatting
      12. Select Manage Rules on the pull-down list
      13. You should see 2 lines:
      Formula: =(M3="No") with an AaBbCcYyZz text on the Red background in the Format column and the =$A$3 in the Applies to column, and
      Formula: =(M3="Yes") with an AaBbCcYyZz text on the Green background in the Format column and the =$A$3 in the Applies to column, and
      14. Click OK to exit Conditional Formatting Rules Manager

      If your M3 contains Boolean values TRUE or FALSE instead of the text, everything remains the same except:
      A. Formula =(M3="Yes") changes into a simple =(M3)
      B. Formula =(M3="No") changes into a simple =(NOT(M3))

  41. Excellent article. Thanks so much. Found exactly what I needed.

  42. Really helpfull...

  43. I have a problem. I want source of data that contains hours in cells, those cells are coloured filled manually when they they are paid. But what i hope to do is use that data to tell me when how much those hours represent in terms of £. To do this I hope you use another tab pulling in the hours and multiple those hours by various hourly rates. I then have a colour function to tell me how much of these are unpaid for a specific period. But I cant figure out how to pull in the colour of the cell automatically, because it might changed, once they are paid.

  44. How do I apply conditional formatting to a row of names to highlight the cell if the name matches another list of names in another tab

  45. THANK YOU SO MUCH. BEST SITE EVER FOR EXCEL TUTORIAL. TUTORIAL TEACHING LIKE ABC.

  46. Hello,
    I need to change the color on the basis the number range.
    Ex:- If =4 should go on Red, if in between 3 to 4 should go on Yellow.
    Expecting answer asap. Please help me on this:(

    Regards
    Ghouse
    +919916964433

  47. Hi,I have a project management file with main file as project list and a separate worksheet for each project with project details. One of the important information is product order date. I was able to format the date cells to change the color format when the date is less than 30 days away.
    But I want to reference that change into the main file so I don't have to review each project tab to see if any of the product deadline is approaching.
    For example Project X worksheet has 10 line items with different order date based on when the product will be required. As the date is less than 30 days away the cell for that particular product line changes to Red to alert me. But in order to get that information I have to review each tab periodically. I want if any of the product order date reaches less than 30 day limit I should get an alert (cell format change) in the main file in front of Project X. (Change in any one of a group of cells will alert the user in main file in one cell only)
    Please advise if there is a way to do that.
    Thanks in advance for your help!

  48. nice

  49. Hi, a little help on formatting that I know should be easy but I can't seem to find.
    I'm trying to change the color of a cell based on if it is higher on lower than the cell before it. I need to do that with every cell. If its higher I need the cell to be green and if its lower I need the cell to be red.
    Any and all help will be appreciated.

  50. Hi team
    I have a row of numbers numbered from 1 to 84 representing 6 years at 12 months per year
    Based on the number in a cell in a column before the start of the years I want to be able to GoTo a starting cell under any number from 1 to 84 and fill in a colour starting at that cell reference and continue to Color say x cells based on the value in anthother cell

    Thanks very much for your help.
    Very appreciative

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)