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 3. Total comments: 426

  1. Its done. Thank you.

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

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

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

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

  6. thx so much

  7. Very helpful post.Thanks

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

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

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

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

  12. THANKS

  13. Very Very Thanks

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

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

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

  17. Thanks

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

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

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

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

  22. Really helpfull...

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

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

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

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

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

  28. nice

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

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

  31. I really loved it and helped a lot

  32. Here is the formula

    TODAY()>DATE(2019,6,19)

    I want the contents of the cell to change colour

    I know it's true

    Conditional formatting does not work....why???

    Please help

    Excel 2003

  33. Is there anything where even one cell is empty and we want to change whole row bg color. I tried too many times but I failed.

  34. Thank you. What I need to to format a range of cells based on one cell.
    IE
    If A1 > 0 then color format A1 (the value = 10) and color format A2 (A1 is over 0)
    I'm THIS close (' ') to figuring it out.
    Thanks

  35. How to change colour of a cell based on the number in the previous cell. Like if I enter number greater than 5 in Column A, in Column B it should be green colour. And if I enter number less than 5 in Column A it should be red colour in Column B.

  36. Very helpful, many thanks :)

  37. Excellent!

  38. Great work! thanks for the help!

  39. Thank you very much.

    This was a big help!

  40. I wish to change the color of whole row on the basis of any particular cell's value. How to do this?

  41. Thank you very much for the instruction, it was helpful.
    but i still have challenge of changing the background color of an empty cell. will like to get another way of doing that.

    Regards

  42. help full thank for sharing

  43. Hi,

    I am working on some Demand and Supply data, where you have the Demand value in ,say, cell C3 and Supply value in cell D3. I need to colour background both cells to the same colour for three possible scenarios,i.e. cell C3 is greater than cell D3 then both cells to have a background colour of amber. Also,if cell D3 is greater than cell C3 then both cells to come up red and finally, if both cells equal each other then both cells come up green.

    I would then need to format all other cells in the spreadsheet.i.e. cells C3 and D3 represent week 1, the next cells, E3 and F3 would represent week 2 and so on. Each line represents a different entity so would need to format the cells down as well as across.

    Sometimes the value of one of the cells can be zero and I need to ensure this also comes up with the same background colour as the cell it i sbeing measured against.

    I have tried the conditional formatting but all the cells kept coming up red !

    Many thanks for your help.

    Kind Regards
    Nick

  44. Thank you so much! This was extremely helpful!

  45. thank you for the information

  46. Helpfull

  47. Very helpful and detailed explanation. Really appreciate it

  48. I would like some help please with conditional formatting. We have an inventory sheet that indicates the items and number of items in the store. I have used the Less than function in the conditional formatting to define a change in background colour when and item numbers is reduced below a certain amount. This amount is variable for each item. We have encountered the problem that when new items are added to the inventory and then they are sorted alphabetically, then the conditional formatting stays at the cell position and is not linked to the items number, such that now the formatting applies to a different item with different limits. How can I resolve this? Is there a way to link the conditional formatting to a item rather than a cell? Many thanks in advance for your help

  49. How do you use the best conditional formatting that shows the differences between the amounts in each cell on a column?
    budget
    237000000
    300000000
    245000000
    250000000

    263700000
    258000000
    260000000
    250000000
    250000000
    250000000
    209000000
    200000000
    225000000
    215000000
    225000000
    225000000

  50. i need to create a formula for conditional formatting. if column D contains "N" then column L should be shaded blue.
    how can i do this?

    1. I need this exact same thing. I have a staffing spreadsheet. We are checking everyone's temperature for coronavirus. I have the number of hours someone works in column D and in column E there is an 'X' if they have been checked. I want the E cell to highlight RED if it has no value while the D cell is greater than 0. Anyone know how to do this? Thanks!

      1. Hello Hayes!
        If I understand your task correctly, to highlight a cell, use the conditional formatting formula

        =IF(AND($E1="",$D1>0),TRUE,FALSE)

        I hope this will help

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 :)