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

  1. Svetlana,

    Thank you for the article. However, I am still trying to figure out a formatting issue. How can I automatically format multiple cells to a certain color if they have multiple values that are less than or greater than a certain value. For e.g., I am trying to format different values with different quota goals listed in multiple cells. Would I need to format each cell individually? I'd appreciate your feedback.

    1. Hello Sushant,

      No need to format each cell individually. You can select the range where you want to apply the conditional formatting and apply it.

  2. Useful information.

    Thanks

  3. Hi Svetlana,

    Can you help me in this,

    I have two columns

    Column A - Numbers
    Column B - Some Remarks to be updated manually.

    If column A1>0 & B1 has some text B1 should not change its bg color
    If column A1>0 & B1 is blank then B1 should change its bg color to Yellow automatically

    Is it possible??

    Thanking you in advance.

    Awaiting for your reply....

    1. Hello,

      Here is the solution.
      1. go to cell B1
      2. click on Conditional Formatting
      3. select Manage Rules
      4. click New Rule
      5. select Use a formula to determine which cells to format
      6. in the box of "Format values where this formula is true:" type
      =and(a1>0,b1="")
      7. click on format select fill and select the color you want
      8. click ok again ok and again ok

      its done.

      enjoy.

      1. Thank you very much Deepak,
        It is working :)

  4. Hi Svetlana,

    I want to change the text of a Cell by changing color of another Cell. Like: If I change the color of A1 to green, then A2 should be written as Correct.

    Kindly help me with a function.

  5. I have one data sheet how can auto find particular record then find the record change the color of cell find the records.

    Thanks

  6. Is there a way to create conditional formatting that would change the color of a cell depending on the number of dates entered into the cell? For instance, I have a teacher that keeps up with her students' milestones by entering a date into the cell each time the student completes a milestone. If one or two dates are entered, she wants the cell to be yellow. When the third date is entered, she wants the cell to change to green. This would allow her to see the date in which a student reached a milestone and when they are completed the milestone. Any help would be appreciated! I'm completely stuck.

  7. Thanks for this helpful article.

  8. what if i want to change the color based on the value of other cell?
    Suppose that if value of F3 cell is greater than E3 cell then F3 cell should fill with red colour. How can i apply formula for that?

    1. Vishwan,

      Create a rule for F3 with the formula =$F3>$E3

      1. Hi Svetlana,
        I just have one problem hope you could suggest some steps.
        We export an excel file from web application we made which is working fine. Now we created an Excel template that we use to fill data. But I am stuck with one problem. When I export the file from web app I have no problem with styling, but when I use template I am unable to set background color for blank cells that are generated in between data. The data generated and is not having a fixed size of rows. Can we create a formula which will detect blank cells and follow the same rules we give for cells with data until the last row of data discovered and from there treat blank as blank.

  9. Hi Svetlana,

    i have highlited a column with 3 conditions and i got all the column filled with 3 different colrs as i needed. Now, i need to calculate no. of cells in each color for all 3 colors. can you help me?
    please.

    thanks.

  10. Hi, will you please help me,

    i want to find cell using find & select feature in excel with color, Format changing is not working with my excel,
    if you have any other way please help me.

    Thank's

  11. Hi Svetlana,

    I have Jan to Dec Sale for Year for 4 separate products and for 4 region.

    I have put vlookup to find sale sale for one product with one region.

    But it needs to be colored In Data sheet.

  12. Let say, we have standard output for various items in one column B2:B10. Daily production numbers for similar items are inserted in other columns C2:D10. For control purposes, daily production number below standard output should be formatted red in font in range C2:D10. How to get it done with Conditional formatting? Please advise.
    Thanks...Aamir

    IN STD Mon Tues
    1 200 190 200
    2 100 110 105
    3 250 240 210
    4 150 160 100
    5 275 274 250
    6 300 290 310
    7 350 360 340
    8 700 690 710
    9 250 255 250

  13. I need my cells to change color when a specific date arrives. Like 90 days before an expiration it changes to yellow and then 30 days prior it changes to red. Please help!

  14. It was very helpful....thank you

  15. I have the conditional formatting set for dates in one column, now how do I get that row to highlight based on that date.

    tia!

    1. Just apply the rule to several columns that you want to have highlighted.

  16. Hi Svetlana,

    I have the following data...
    A1=20
    A2=12
    A3=9
    then
    B1=19
    B2=13
    B3=8
    I want that when the values in column A are smaller than the adjacent values in bolumn B, the figures in column A change colour. In this case, A1 and A3 should turn into a different colour.

    Thank you before hand for your help.

  17. I have a requirement whereby I want Cell Q21 to turn one of two colours based on the result that appears in Cell Q22.

    So if the result in Cell Q22 is equal to or greater than the value in Cell E20 I want Cell Q21 to turn "green"

    But if the result in Cell Q22 is less than the value in Cell E20 I want the Cell Q21 to turn "red"

    Can someone please advise a formula that can achieve this.

    Thank you.

    1. Hello Perry,

      You can create 2 rules for Q21 with the following formulas:

      Green: =$Q$22>=$E$20
      Red: =$Q$22<$E$20

  18. Svetlana
    I can't figure out how to make a cell color change when A1 is 10% greater that A2. I would like A2 to change its color

  19. i want this type of date& day format but i can't create this,please help me:-

    Month---March-15

    Dispatch Date Day
    01 Sunday
    02 Monday
    03 Tuesday
    04 Wednesday
    05 Thursday
    06 Friday
    07 Saturday
    08 Sunday
    09 Monday
    10 Tuesday
    11 Wednesday
    12 Thursday
    13 Friday
    14 Saturday
    15 Sunday

    the formula contain B2(for select the month & year)also contain the formula A5 & B2(i.e contain result 1,2,3,4,5 etc.etc.)
    and day contain formula is on table format(i.e cloum A & B,that's result day sunday,monday etc.)

  20. Hi...Svetlana Cheusheva

    Thanks for all information.
    This is very useful to me.

    Again thanks a lot.

  21. Hi Svetlana,

    I have requirement where in i want to color the cell automatically when i have added some value to other cell. how can i do that?
    like i want to color column X automatically when i will add some value to column Z.

  22. Hi Svetlana,

    I have requirement where in i want to color the cell automatically when i have added some value to other cell. how can i do that?
    like i want to color column X automatically when i will add some value to column Z

  23. Hi Svetlana,
    Thanks for all this info.
    I would like to change the colour of an entire row not just a cell, based on contents of a cell in that row. ie if the specified cell contains a specific word, the entire rows fill can be changed.
    Thanks
    Walter

  24. Can you please attached a filee as reference because i can not follow up with all the comments?

  25. Hi
    I am completely lost when it come to VBA and I need help.
    I have a folder where I save a lot of reports with the same excel format and then I have a master file where I copy and paste all the data from all the folders in. So I have tried a lot of things but cant get the master file to automatically copy the data from all the workbooks.I think where it becomes complex is the workbooks have drop down lists and some color formats in as well in some columns. So what I would like to do is the master file copy all data and past special from the other workbooks and if possible delete them after closing. Please I need help.

  26. Hi Svetlana,
    This is very useful

    I have one question,How to change the entire column color based on one cell value? eg if i have a text sunday in a cell, the entire column should be displayed in different color

    1. Hi Hari,

      You can select all the columns you want to highlight and create a rule with the following formula:

      =countif(A:A,"Sunday")>0

      Where A is the left-most column.

  27. Its very use full for me

    thank you

  28. OK Thanks a lot and for your time.
    I did google a lot but did not get any clue. You made it clear :)
    Thanks dear!
    Regards
    AMK

  29. Hi Svetlana,

    Is it possible to change color of cell by using formula alone and not using any conditional formatting.

    Regards
    AMK

    1. Hi!

      Nope, formulas can perform various calculations and insert values based on the conditions you specify. To change cells' color you need either conditional formatting or VBA.

  30. Thanks so much...

  31. Hi Great help,

    Just one thing how do I get five category and five colours the conditional formatting is only offering 3 .

  32. Hi Svetlana,

    IS this possible also to put some error message in the cell or in some other cell based upon conditional formatting. let say If cell of column A in a row has value less than 10 then cell of Column B will be highlighted as well as we need to put message that "column A is < 10" either in the same B cell or in different column cell in the same row?

    1. Hi Swapnik,

      You can display a message by entering an IF formula in cell B1 and then copying it down to other cells:
      =IF(A1<10, "column A is < 10", "")

  33. thank you

    It's very easy to know

  34. Hi

    I am fully impressed. it's very useful to me thank u very much for simple logic's

  35. Hi Svetlana,

    I have a workbook with multiple sheets of training events. Each sheet of of events makes up a job. All events must be current in order to be qualified for the job. I have conditional formatting set up now to have the training date entered in a cell be green if over 30 days from expiring, yellow if within 30 days and red if past do. I have a master sheet at the beginning of the workbook with a list of all of the employees. I want to list all of our jobs next to their names and have a cell for each job turn red if any of the date values from any of the job sheets turns red indicating something in their jobs qualifications is past do. Is this possible?

  36. Thank you so much..really i got the solution

  37. what is the formula if the condition are
    if the report delivered after 13 of every month than the cell shown red color.can u send me photo like a screen shot

    Thanks

    Best Regards

    Inam ul haq

  38. Typo in the above:

    I need to know "IF" a maximum of 3 has been....

    1. Hi Kristen,

      Assuming that the values you want to highlight are in column A and row 2 is your 1st row with data (not including column headers), you can use the following formulas:

      Highlight the 2nd occurrence: =COUNTIF($A$2:$A2,$A2)=2

      Highlight the 3rd and all subsequent occurrences: =COUNTIF($A$2:$A2,$A2)>=3

  39. Hello Svetlana,

    I was wondering if it is possible to get cells in a column to change colour based on whether a word occurs 2 or 3 times in that column. For example, I need to know is a maximum of 3 has been reached (typed into the workbook) so if any word occurs 2 times anywhere in the column I would like it to turn yellow, and if that same word occurs 3 times I would like it to turn red so that I know this item is exhausted. Is this possible?

    Ex. If a given suburb, say "Sydney", has been entered by my sales reps into a column on a shared workbook 3 times I would like to to turn red so that I know it is completed, and if it has been entered 2 times I would like it to turn yellow so that I know it is almost completed.... complicated I know!

  40. Hi Svetlana,

    I noticed that you are a genius in excel and very helpful to others. I was wondering if you could help me out.
    I have a table of numbers and need to fill each cell with a color based on if the previous number increased or decreased. So if 250 (A1) gets changed to 254 (A1), I want to fill in that cell with green because it increased. BUT if that same cell, A1 (254) gets changed to 253, I want the cell to fill in with red because of the decrease.
    I am wondering if this is possible to format for each cell.

    Thank you in advance,
    Tommy

    1. Hi Tommy,

      I regret to tell you that it is not possible. Excel conditional formatting formulas can compare a value in a given cell with some other number, or a value in another cell, or a value returned by some other function. But it cannot respond to a value change in the same cell.

      1. Ok, thank you for responding so fast! Is there any other way to fill in the cell based on an increase or decrease, that wouldn't have to be applied every day?

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

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

  43. Hi Svetlana,

    It is really helpful, Thank you

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

  45. thank you its help me....

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

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

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

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

    Thank You so Much...

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

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