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

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

  2. Really helpfull...

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

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

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

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

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

  8. nice

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

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

  11. I really loved it and helped a lot

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

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

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

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

  16. Very helpful, many thanks :)

  17. Excellent!

  18. Great work! thanks for the help!

  19. Thank you very much.

    This was a big help!

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

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

  22. help full thank for sharing

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

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

  25. thank you for the information

  26. Helpfull

  27. Very helpful and detailed explanation. Really appreciate it

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

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

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

  31. Hi,

    Can you help me please!
    All I need to do is change the colour (green) of a single cell on a spread sheet if the value of multiple cells in a row are greater than 0. when I try only the first cell in the row changes the single cell colour all the others have no effect.

    Kind regards

    T

  32. Hi,

    How can I condition format rentire row(s) based on the content of one cell/column? E.g. make the entire row green if value in one cell is less than a number?

    Thanks for your help.

  33. Hi,
    Is there a way to change a blank cell color to red, for example, based upon the value placed in another cell, then turn the red cell to clear once data is placed into the red cell?

  34. Hi there,

    So my situation is kind of similar, but different.

    I am trying to change the formatting (color) of a number of cells in an array (4 cells in a row) given a value that I am going to input in 1 of those cells. This input will be compared to an array of values already input. So depending on the value of the cell I am inputting, if it is lesser than my base I want it to be compared to, the row will be come green. Or stay white.

    This is the rule formula I was thinking about, but can't find better.

    =IF($C10+$D10=125, $AF$5 (cell to be compared to), IF($C10+$D10=150, $AG$5, IF($C10+$D10=225, $AF$6, IF($C10+$D10=250, $AG$6, IF($C10+$D10=275, $AH$6, FALSE)))))

    And, it does not work. It doesn't adapt dynamically.

    Any thoughts?

    Thank you.

    1. Hi there - I was wondering if you've found a solution to this issue, as I have recently run into it myself! Its quite frustrating. I'd love if you could share any progress you've made.

  35. its very helpful, thanks a lot !

  36. Hi and thanks for the blog post,

    Is that possible to modify the color of a cell regarding the content cell hexadecimal value (without coding) ?

    and also modify the cells on the right regarding the different shades of a color (such as https://www.crazy-colors.net/en/hex/1e90ff) ?

    thanks!

  37. Hi,
    Thank you ma'am. It is very helpful :)

    1. Ma'am,
      If I want to apply the colors based on the maximum and minimum values of each row independently for a huge number of rows in a go, what can I do?

  38. Hello,

    I need to create a rule where if one cell contains specific text ('Yes'), it highlights in one colour (yellow) and then if another 'Yes' is present in the cell next to it (same row), then the second 'Yes' cell will turn Orange and the red for a third 'Yes'.

    Is this possible?

    Or - (for example) if C1 contains 'Yes', then a A1 and B1 turns yellow, and if D1 also contains a 'Yes', the A1 and B1 turns orange and so on.

    Thank you in advance!!

  39. I would like to format one of the columns in my spreadsheet to highlight if there is a value present in another column. What I am trying to do is show whether materials are present or not and I would like to show an indicator in one of my first columns.

    Thank you!

    1. Hello, Desiree,

      you need to use conditional formatting based on another cell value. Please take a look at this article of ours. If you pay attention and follow the steps, you'll manage to solve your task :)

  40. thnx for all of these

  41. Hi,
    I want to change 15 cell colour if another cell is blank(no value).
    I tried by using condition format " format only cell contain", but it some time it working and some time not working.
    Please help me to fix issue.

  42. Hi Excel novice here.
    I need a cell to change colour if the total of a column is between 2 numbers. How would I go about doing that?

    1. Hi, Gavin,

      here are a few articles for you to check out in order to solve the problem:
      1) these basic formulas and functions will give you a great understanding of the logic of all the Excel operations and calculations - will help to set the right criteria (if the total is between two numbers)
      2) conditionally formatted rule will colour the cells depending on some criterion
      3) here are the ways of building formulas to sum the values

      I do hope that you'll take a look at this great and easy tutorials and will be able to solve your task :)

      1. thank you

  43. Hi Svetlana Cheusheva,
    Thank you so much,
    your article is really helpful.
    I have problem that,
    How to add "Ablebits" option?

  44. Greetings,

    I want to highlight a row in the following manner:

    1. Columns C, D, and E will have an X entered in them and they are for good, not good, partially good.

    2. When I put an X in the good column for that row, I would like the row from columns A through J to highlight Green, Not good = Red, partially good = yellow. If there is no X in either 3 of these columns then the row is blue. If all 3 have an X then it should also turn Red.

    Is there any possible way this could be done through excel?

    Thanks in advance.

  45. hi.. Please tell me if i have a huge data and there is some coloured cell so how can i copied or filtered only those cell which are coloured.

  46. How to change the color of States column (column A) automatically according to the color of conditional format applied in other column (Column B)?

  47. thanks i problem solved

  48. Hi use this site as I am just more than a novice with Excel but this helps me set what appear to be impossible tasks with ease, all explained in laymans terms.

    Many Thanks

    Malcolm

  49. Hi,

    I've a array of data (600 rows X 300 columns) want to compare 2 rows of data with conditional formatting and change color. The problem I facing is I can't able to write formula for each cell. If u know any special formula which can be used for entire array, kindly us know.

    For Eg, Compare A1 & A2 if A2 is less than A1, change color. Similar need to do for A3&A4, A5&A6, B1&B2, B3&B4, B5&B6, C1&C2, C3&C4, etc...

  50. Don't why below scenario did not post...seeking auto solution

    B3 is red if less than B2
    B3 is green if greater the B2
    B4 is red if less than B3
    B4 is green if grater than B3
    ect...

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