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

  1. Dear Svetlana,

    Congratulate for your blog, I am fully impressed.
    I tried to find an answer and maybe you already had this question - if yes, sorry.

    My question is: I have an excel sheet where costumers populate entries time by time based on general rules, so the entry is fixed. I have a mapping table about 20 countries. I would like to colour only these countries on my main sheet with conditional formatting or a rule-set but without an additional column where I use vlookup. I can set up 20 different rules with conditional formatting, but maybe you have a more professional idea as well.

    Many thanks for your kind help in advance, Kate

    1. Hi Kate,

      This task is far from trivial :)

      You can try creating a rule with the following formula:
      =NOT(ISERROR(MATCH(A2,Sheet2!$A$2:$A$20,0)))

      Where A2 is the first cell with data in your main table and A2:A20 is the list of countries in the mapping table.

      1. Dear Svetlana,

        You are a genius, it works!!! Thank you very much!

        Kate

  2. What a wonderfully helpful post, and active for so long!

    Now, I have a question. I have a formula to calculate dates out: =WORKDAY(B1+(5-1),1,holidays) So, it will only return weekday results that don't fall on my defined holidays. Is there any way to make it so that when a date is "pushed forward" to a non-holiday weekday, that the cell would change color so you could see at a glance that the date had been pushed forward in that manner?

    Thanks!

  3. Hi Svetlana,

    It is really helpful, Thank you

  4. dear Svetlana,

    i have a question and hopefully you can help me. i have a file in which we have a consequence of numbers in range of 50 (...01-...50; ...51-..100 etc). please find example of how they look below.
    140247951
    140247952
    140247953
    140247954
    140247955
    140247956
    140247957
    140247958
    140247959
    140247960
    140247961
    140247962
    140247963
    140247964
    140247965
    140247966
    140247967
    140247968
    140247969
    140247970

    is it possible to use condition formating to higlight let say 140247970 when i search it through find box (CTRL+F). these are barcodes that we have generated and we want to track them when they have been returned to me.
    hopefully you can help me as i am not very good in these kind of formating.

    thanks in advance :)

  5. thank you its help me....

  6. how do i create a formula where
    if cell B1 = letter z or Z , and no other letter (remove blanks, then change cell color to green

  7. Hi Svetlana,

    I want to know wheather top three selection, or bottom three selections can be displayedin three different colors of our choice

    Thanking you

  8. Hi Svetlana, you have many satisfied customers! I read the couple of hundred posts in your forums and did not quite see the issue I am having. I receive a weekly report and I need to compare 5 columns of data in this week's report vs. the same 5 columns in the prior week's report, and identify if any changes occurred in each row for 900 rows. I use a formula (below) where Column A looks at Column F, Column B looks at Column G, Column C looks at Column H, Column D looks at Column I, and Column E looks at Column J, for any changes (text or number). I put the formula in cell K2 and copy it down to K900. The formula in Column K tells me that some data has changed in the row if there was any change, but I have to visually look at all 10 columns in the row to see what cell(s) data changed. Is there a way to color the cells where the data has changed? Here is the formula I use to identify changes:
    =IF(SUMPRODUCT((A2:A900=F2)*(B2:B900=G2)*(C2:C900=H2)*(D2:D900=I2)*(E2:E900=J2))>0,"","Changed")

    I hope you can help! Thanks

    1. Hello George,

      Thanks for the detailed description.
      If you simply need to compare 5 columns in one row, just select A2:E900 or F2:J900 (according to the part of the table you want to color) and create a conditional formatting rule using this formula:
      =A2<>F2

      If the group of 5 cells may be in different rows, then please send your sample workbook to support@ablebits.com. We will try to find the solution.

      1. Hi Alexander,
        I actually need to compare 10 columns in one row. I tried the formula you mentioned and variations of it using yellow shading but all it did was shade all the cells yellow. I will send a sample workbook to the email address you provided along with additional clarification of what I need. Thanks.

  9. Thank You so much we solve over problem, before we don't know how to change background color..

    Thank You so Much...

  10. Hi,

    I need help in highlighting multiple cells till a certain value is reached:
    Ex.

    stock demands w01 demands w02 demands w03 demands w04
    3000 500 1000 1200 1000

    I need that Excels highlight first 3 postions because their sum is less than the stock.

    Would someone help me pls?
    Thanks

  11. Hi,

    I am creating a spreadsheet and would like to have three collums with dates that change the color of each row.

    colloum A - turns X color when a date is entered.
    COlloum B - Turns X color when a date is entered.
    Colloum C - Turns X color when a date is entered.

    I would like a check mark to apear in a fourth colloum when the row has three dates entered, and a X when one or two dates are entered.

    Thank you.

    1. Hi Aaron,

      You can create a rule for each column in this way: Conditional Formatting > New Rule > Fromat onl cells that contain and choose "No blanks" from the left-most drop down list.

      Then, enter the following formula in the 4th column:
      =IF(AND(A2<>"", B2<>"", C2<>""), 2, IF(AND(A2="", B2="", C2=""), "", 1))

      Where A, B and C are your date columns.

      Finally, create the following icon set rule for the 4th column:
      Icon sets rule

  12. Hi, I have created a worksheet that produces division of an input number by the numbers 1 to 100 in individual cells. I am trying to turn change a cell colour for cells where the product of the division is an integer. I have checked and there does not appear to be a direct way to do this with the standard formulas. Could you help please.

  13. hi,

    i wanted to change the colour of a row based on a specific value of a cell. can i do so?

  14. with an using conditional formula, cell A1 results "passed" with colour letter after putting a date in Cell D1. Please...

  15. Hi there

    I have a set of data in a table and all the numbers are different.

    I'm sending this to someone else and if they wish to manually change the number to something else I want the cell to change colour.

    Is there a way of doing this with conditional formatting?

    Thanks for your help

  16. Svetlana,

    I have a slightly different question, though very related: I would like for cell A1 in Sheet 1 to be red if it is greater than A1 in Sheet 2, and green if it is less than A1 in Sheet 2. I would also like cell B1 in Sheet 1 to change color based on B1 in Sheet 2, and so on.

    I can do this manually, using your instructions for "Format only cells that contain," but I have thousands of cells I would like to change in this way. It may be obvious that I am looking for trend changes, and normally I would use a line chart, but I want to know the exact numbers where the changes take place, and Excel charts don't seem to allow for that kind of detail.

    Thank you for any help you can give.

    Robert

    1. Svetlana,

      Nevermind, I figured it out, playing off your suggestion to question #59 (and the basic advice to use the conditional formatting button, of which I had been unaware before I found this post). I used "Use a formula to determine which cells to format," and typed in the formula:

      =A1'Sheet 2'!A1

      For the first formula I selected one color (though it looks like I could have done just about any formatting) and for the second I selected another.

      If you highlight the whole area you need to follow this formatting, and make sure not to use dollar signs on any of the cell coordinates, the formatting should appear across your whole worksheet.

      Thanks for getting me started!

      Robert

      1. It looks like I lost a few lines, including the second formula. The first one should have a "greater than" sign after the first "A1," and the second is identical to the first, except it has a "lesser than" sign:

        =A1'Sheet 1'!A1

  17. I have Repeated elements
    5347940
    5347940
    5347945
    5347945
    5348035
    5348035
    5348036
    5348036
    5348053
    in this i want to color alternate numbers

  18. thanks it has been helpfull

  19. Hi Svetlana,

    I am trying to set up a database for training records of employees,

    using your rule above is it possible to set a cell to change green, yellow or red based on the training course expiry length.

    For example Cell E13 is Green, if the date in cell F13 is 60 days away from the course expiry date set in G13 then Cell E13 turns yellow, if it passes the date then it turns red

    Any help is much appreciated

    Thanks

    Craig

    1. Hi Craig,

      Try creating a rule for cell E13 with the following formulas:

      Red: =$F13>$G13
      Yellow: =AND($G13-$F13>0, $G13-$F13<=60)
      Green: =$G13-$F13>=60

      If you need to highlight more than 1 cell in column E, select them all before creating a rule and replace "13" in the above formulas with the number of the top row in the selection.

  20. thx for help XD

  21. Thanks a lot
    it was so helpful for me

  22. Can you use this to change a cells color based on a word in the cell instead of a value?

  23. Hi,
    Is there a way to select a random cell in excel and have all the other cell with the same value highlight? So, once I select another cell, the originla highlight is lost and all the cells with values same as the new selected cell gets highlighted.
    I need this for work to make my presentation look spot on and set me apart from the rest. If you could reply back, I would very much appreciate it.
    Thank you!
    Shweta

  24. Svetlana,
    Thank you for your post. It is very useful. You seem to be an expert in excel color and I wonder if I can ask you a question about Excel color. I inherited an excel file with a pale yellow/sand color. When I check the color index, it shows -4142. When I change the color of the cell, it shows the normal color. When I set as no fill, I get this color (-4142). When I print the sheet, the color displays similarly as a no filled cell. I wonder if you know how this -4142 "color" gets set in the first place?
    Thank you in advance.

    1. Hello Tina,

      I am hesitant to give any advice without seeing the formatting. If you can send us your workbook at support@ablebits.com, we will hopefully be able to help.

  25. Thank you very much

  26. Hi Svetlana,

    I am very impressed with the creative solutions I have seen on this page.
    I am hoping you can help me with a problem I have been struggling with for months.

    I would like to copy the contents and format of a column of cells and paste them in another column using a formula.
    All columns are formatted as type = General
    Column A is a list of random IP addresses that are color formatted as green or red cells. Just say every other IP address is green and the others red.
    Column C is also a list of IP addresses (a master list) with no color.

    In column B, I want to take all the IP addresses in column A and line them up on the same row as the IP address in column C (and keep the color formatting from column A).
    In column B to get the IP address from column A to line up with the IP addresses in column C, I used the formula:
    =IF(ISNA(MATCH(C1,A:A,0)),"",INDEX(A:A,MATCH(C1,A:A,0)))
    This works but column B does not have the color formatting as the cells in column A
    Is there any way to keep that formatting when using a formula?

    Here is the data (remove column B if you want to check the formual):
    192.168.36.21 192.168.36.21 192.168.36.21
    192.168.32.31 192.168.33.174
    192.168.35.0 192.168.32.31 192.168.32.31
    192.168.37.56 192.168.40.163
    192.168.45.10 192.168.42.82
    192.168.43.221 192.168.35.0 192.168.35.0
    192.168.45.108 192.168.37.56 192.168.37.56
    192.168.46.208 192.168.38.105
    192.168.32.226 192.168.38.214
    192.168.39.208 192.168.43.224
    192.168.32.246 192.168.45.10 192.168.45.10
    192.168.35.109 192.168.43.221 192.168.43.221
    192.168.34.99 192.168.46.215
    192.168.32.55 192.168.45.108 192.168.45.108
    192.168.46.208 192.168.46.208
    192.168.32.226 192.168.32.226
    192.168.43.42
    192.168.39.208 192.168.39.208
    192.168.32.246 192.168.32.246
    192.168.34.23
    192.168.36.68
    192.168.35.109 192.168.35.109
    192.168.34.99 192.168.34.99
    192.168.32.55 192.168.32.55

    Maybe this is not the right approach and using some other way like Offset would be the way to go. Any input is welcome

    Thank You for your valuable time,
    Sven

    1. Thank you, Thomas! Much appreciated : )

  28. Hello,
    I'm trying to set up a sheet where if I enter something in A (will be a number) then B,C,D,E turns red until something is entered into each one.
    Once a value, or word is entered in each of B:E they turn back to white.
    Is this possible? It seems like trying to connect 2 formulas may be contradicting

  29. I am partially red/green colour blind and rely on the labels that appear when I use Fill on the Home ribbon (pause over a colour and the label appears). But those labels do not appear when I use Fill from the conditional formatting feature. How do I get them to appear when using conditional formatting? Also how do I get labels to appear in 'more colours'?

    1. Hello Kevin,

      I am very sorry, I don't know a way to make the labels to appear for conditional formatting colors.

  30. Hi Svetlana

    I have a range of values in Row1 - say A1 to J1
    I have another range of values in row2 - say A1 to J2.
    I am comparing A1 and A2; then B1 with B2....... then J1 to J2.
    Whenever value in row1 < row 2, the row2 cell should be changed to red color.
    This is needed to be done across the entire row 2.
    Kindly let me know the solution.

    1. Thank you Svetlana!!

    2. Hello Sridhar,

      If you have to highlight each cell in row 2 that has a value greater than the value in row 1 in the same column, then select row 2 and create a rule with this formula:
      =A2>A1

      If you have to highlight the entire row 2 if at least 1 cell in row 1 has the value less than in row 2, you need a helper cell, e.g. A5, with the following array formula (remember to press CTRL+Shift+Enter instead of Enter to complete it):
      =SUM(--(A1:J1<A2:J2))

      After that, select row 2 and create a rule with this formula:
      =$A$5>1

  31. Hi,

    I have applied conditional formatting to change the color of few cells to red in a row based on a value. After applying conditional formatting,if i copy and paste some data to these cells, the background color changes to white. Can you please help?

    1. Hello Satish,

      When you use the standard Paste command, the current rules are replaced with the ones applied to inserted cells.

      You need to use the Paste Special option instead: right-click on the destination cells, select Paste Special from the context menu and choose the Values or Formulas radio button under Paste.

  32. Thank you so much, it is very useful!
    I also want to create legend after doing these way, how to create leagend?
    Thank you indvance!

  33. I am tracking training of people on my project. All of the training they take has to be retaken annually.
    Based on the completion date in a cell I want to turn the cell GREEN if the number of days fall within 1 to 334 days from the completion date. I want to turn the cell YELLOW If the number of days fall within 335 to 364 days from the completion date. I want to turn the cell RED if the number of days is 365 or more from the completion date.

  34. In col. B2- 100. In col.A3-101,A4-102,A5-103. In col.C3-99,C4-98,C5-97.
    when col.B2 there are 101 i want green color in A3.
    when col.B2 there are 102 i want green color in A4.
    when col.B2 there are 103 i want green color in A5
    & Viseversa
    when col.B2 there are 99 i want red color in C3
    when col.B2 there are 98 i want red color in C4
    when col.B2 there are 97 i want red color in C5

  35. i want to color some selected cells based on cell color
    please let me how to do it

    Thanks

  36. i want to color selected cell based on cell color

  37. hi the post is very helpful to me bcoz I have learnt a new thing but I got one question how do you do if you want to color the cell after entering the value maybe less than 3 to color yellow and greater than 3 or empty cell to remain with default color

  38. Lets say I have a formula in a cell from another page in the workbook. The cell in the other work book is a drop down. Is there any way when that drop down is used/filled to make the cell on the second page change color to show its populated?

    1. Hello Steph,

      It looks like you need a special macro to fulfill this task. I am really sorry we can't help you with this.

  39. Thank you, this topic is helpful

  40. Is there a way to format a column of numbers to highlight only when another column has an "X" in that row?

    1. Hi Patricia,

      Of course, there is a way. Simply select the column you want to highlight, not including the header and create a rule for it with this formula:
      =A2="X"

      Where A2 is the first cell with data in the other column, again not including the header row.

  41. You're the best! Thank you doll

    1. Dear Svetlana Cheusheva,
      Can we color a cell based on result. In detail, I am using a formula (=min(various nos of cell in a particular row) to find the lowest figure , Like ( =MIN(W7,S7,O7,K7,like so on))and for example result is W7 cell and I want to automatically color the W7 cell. By conditional formatting it can be done by selecting entire cell range, but problem is I have to more than 40-50 row and I have to repeat the same rule for each row. Is there any help for me. Pls guide.

      1. Hello Rameswar,

        You can select all the rows and create the conditional formatting rule with the formula =A7=MIN($W7, $S7, $O7....)
        Where A7 is the first (top-left) cell of your range.

        But I'd recommend creating a helper column (hidden) and copy the following formula there: =MIN($W7, $S7, $O7....) And in your conditional formatting rule, use the formula =A7=$AB7, where A7 is the top-left column and AB is the helper column.

        If you have a large data set, this rule will work much faster because Excel won't have to calculate =MIN($W7, $S7, $O7....) for each cell where the rule is applied.

  42. Thank you for your response, but i might not have explained it well enough.
    I have 3 cells that need to change rules according to what i have selected from a drop down list. So, if i have "refrigerator" selected from the list, the 3 cells need to have one rule for that selection, and if i select "freezer" the same 3 cells need to have another set of rules. Not sure how to do it.

    Thanks for you help though! :)

    1. Hi Ron,

      Regrettably, Excel conditional formatting rules cannot be changed depending on the value selected in the drop down list (this can be done using VBA only).

      As an alternative, you can create several rules for the same range of cells, one per each value in the drop-down list, that will check the contents of your drop box and color the cells you want depending on the drop-down list's value and your additional conditions.

  43. Hi,

    Can I have a complete row highlighted in any color based on if there is any change (addition or deletion) of data from the exiting value?

  44. very nice , thank you.

  45. Please Please Help!!!
    My cell C1 has to say "yes" if A1>100 and B1100 and B1>70.
    please help how to make this formula ??? My head is broken by now :(

    1. Hi Sergey,

      Here you go :)

      =IF(AND(A1>100, B1100>70, B1>70),"yes","")

  46. I am creating a task list and I would like to change the fill color of the row depending on to whom the task is assigned. I have created the drop-down list and used conditional formatting to assign colors to the individuals names. Now when I select a name from the drop-down list the fill color changes for that cell. How can I get the corresponding cells in the row to change to the same color?

      1. Thank you for taking the time to assist me. It is much appreciated.

  47. i want to color some cells based on different cell's condition. I've done it but the problem is when i close the document after saving and open again then i see that the condition has been erased. but why??

    1. Hi Tanjin,

      This is absolutely abnormal! Please make sure you save the workbook after creating the rule.

  48. Sorry, i meant color coded values

    1. Hi Ron,

      You can select a group of sells you want to color (e.g. D10:D12) and create 2 rules with different fill colors based on these formulas:
      =$D10="Refrigerator" and =$D10="Freezer".

  49. How can you make a group of cells change their rules based on what you select from a drop down list??

    I have a drop down list consisting of "Refrigerator" and "Freezer". I have coded values in "D10-D12" that need to change based on whether i have "freezer" or "refrigerator" selected.

    Thanks!

  50. Hai,

    I have a trend monitoring datasheet . Each kind of trend refers to 1 - 100 numbers. i need to highlight the trend if it occurs more than 2 times.
    Eg. Trend No.1 - BY Road, 2 -By shop, 3 -By School ... up to 100 numbers. If trend No.1 occurs two times, it should highlight. Can you please help me.

    1. Hi Anil,
      To be able to suggest a proper formula, I need to know more details. Is each trend a column or a row? Do you want to highlight certain cells or entire rows? Please send me a workbook with your sample data at support@ablebits.com and I will try to 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 :)