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

  1. Congratz. This is very helpful.

  2. Dear Svetlana cheusheva

    I need an formula for changing the background color in Excel sheet if the value changes then the color of background automatically change using excel formula .Kindly revert if any information required

  3. Dear Svetlana cheusheva

    I need an formula for changing the background color in Excel sheet if the value changes then the color of background automatically change using excel formula .Kindly revert if any any information required

  4. Hi SC,

    This article is really helpful.
    I just want to know
    Is it Possible to change the color of the text in this way?

    1. Hello Ali,

      Of course, it's possible. When setting the format, switch to the Font tab instead of the Fill tab, and select the font color you want.

  5. I want color in cell according to value as like tank level indication

  6. Thank you so much... It helped me a lot. I got a great applause from my team. thank you... :) :) :)

  7. Hi
    Could you please advise? How cell will be auto highlighted if we will do any amendment.
    i have raw data and values are there but in order to make a report i need to change some values. But after that report i want original data. So if there will be auto highlighted function then i can see the cell easily and put the original value back.
    Looking for your urgent reply.
    Thank you

    1. Why not keep a master copy of your document and then work only on a copy, so that changes to the copy don't make change to the master document?

  8. Hi Svetlana,

    Thanks for wondering excel formatting techniques. I would like to know about colour change in excel formatting, When a cell colour is already coloured by conditional formatting, when task changes, i want the colour of that cell to be changed. Could you please give some ideas to that.

    thks in advance

    rahul

  9. How to add 4rth condition as i want to make 4 conditions for a cell to change color

  10. Hey ,
    Can some one help to achieve this task :

    I need to change color of A1 cell based upon following criteria .

    if Cell A2 to A10 value is = Y
    then
    color of A1 cell will be Red
    Else
    color of A1 cell will be Green

  11. thanks a lot

  12. thanks alot, for the information

  13. Hey,
    I want excel to change the color of cell in one sheet when value of it's reference cell has been changed in another sheet.
    e.g.
    sheet 2 contains values referred from sheet 1
    and sheet 2 is completely protected.
    Then any value in particular cell modified in Sheet 1 shall displayed with another background color in protected Sheet 2

    Please let me know, if its possible.
    Thanks.

  14. I have B1 through B400 that I need each cell to change based on cell A1 through A400. I do not want to format each cell manually. Copy and past has all the B cells referance A1. What am I doing wrong?

  15. I have two coloum in excel one order qnt and obe export qnt and I want after full qnt export automatic course change of qnt.

  16. Dear sevetlana,

    Tell me can i put sell value according to name like 'open'and 'closed'.
    and if yes then how.

    Ajay

  17. Finally some easy to follow steps. I needs this time to time and I'm often struggling to achiev what I wanted. This one was awesome ;-)

  18. clear n precise...very very useful

    Thanks a ton

  19. Hi Everybody,

    I need your help.I wanna to fill colour only numeric in excel.
    Number is 10000 between 20000.This numbers are fill colour after choice.
    Thus, If it's not ok.
    Give me another one method, and how to write macro.
    Please explain me!

  20. I need to change the colour of cell as follows

    A B C
    1 38 40
    2 38 42
    3 38 35
    If B1 is greater than or equal to A1 the colour of C1 should be green otherwise it should be red. Pls help

  21. Thank you very much.

  22. This Article is very helpful and it contains many solutions for excel.

  23. Am prepare timeline chart for project. i need to highlight sundays dates.how to color Sundays by conditional format

  24. I have 2 column's one for start date and the other end date - and next coloum gives the numbers of days between the start date and end date ..
    if i say
    Column E -- start date
    Column F -- End Date
    Column G -- no of days

    If i want to get the cells in Coloumn G coloured for all the row which donot have a end date ..... how can I format it .

  25. This is awesome, thank you.

    I am wondering: I have a group of cells in which I have numbers, and I am usin the MIN command to find the lowest number between these cells. I would like to have a cell next to the one displaying the lowest number then display a block of text and a cell color based on which of these cells has the lowest number, so I can basically show a "winner" of which number is the lowest in the data set. Is this possible?

    So essentially it looks like:

    1 2 3

    Thank you!

  26. I would like to conditionally format cells that contains a date, based on values in different cells. My cell contains a date (indicates due date) and the gradient bar behind it would indicate percentage complete in color (stored in a different cell). Can you provide some help please?

  27. I want line charts details ,with green colour line shows if its upward and red colour line shows if its Downward ,one line in one chart

  28. I want chart details for I use two column plan and actual, plan > actual green colour ,and plan < red colour , how to form methed

  29. Hi Svetlana,

    Actually i am in "sheet1 cell a1" and I want this cell colour to be changed based on the value of "sheet3 cell n12". Can you please help me out. Thanks

    1. Thanks I got just now...

  30. This article is super helpful. I'm trying to do something based off of your writings and...it's not working. I wonder if I'm going about things completely wrong.

    I have a list of student levels on a worksheet from one semester, I have made a duplicate for the next semester, the new values are entered in semester 2 - and want anybody who has moved up to level 4 (Levels run 0 Bad to 4 Achieved) to be highlighted green.

    I.e. If you have moved up to level 4 between semesters, you are colored green.

    I tried conditional format in the second semester
    =AND(Semester1!$A$1:$D$29<4,Semester2!$A$1:$D$29=4)

    Format: Fill Green

    I don't know if it's because the data is on two sheets (I could put it on one) or if it's the way I'm using the range. Or that I'm being too basic and should just color in the cells myself!

    Sorry if I have gotten all this completely wrong. It's nice to find someone on the interweb who makes sense!

    Stephanie

    1. Hello, Stephanie,

      To help you better, we need a sample table with your data in Excel. You can email it to support@ablebits.com. Please add the link to this article and your comment number.

  31. Thanks,

    It is really useful for us ...

    Thanks again

  32. i want this cell number 1 to 100 but 15 is grater then color green and other red color then use formula excel sheet

  33. DEAR SIR,

    I AM USING ONE IF CONDITION FORMULA, WHICH RESULT IS PASS OR FAIL.

    I NEED THE RESULT WITH COLOR. EXAMPLE.PASS WITH GREEN BACKGROUND AND FAIL WITH RED COLoUR

  34. Thanks a lot for adding the drops of knowledge in my excel skills,
    I respect as like as Teacher,

  35. This help me. Thank you

  36. Many Thanks Easy to understand.

  37. I would like to highlight cells which end in a certain number. Cells ending w/ 0,1,2 colored yellow; 3,4,5 green and so on

  38. i want whan type BUY than cell become Green and whan type SELL than cell become Red pls help on that

  39. Hi,
    i have one set of numbers in one cell ex: 32 48 52 55 57 59, now i want to color only 32 in this array
    please let me know how to do......

  40. i want whem i write the world ( done ) the raw will be automatically green .... how can i do that

  41. Useful Article.Thanks...

  42. I have three cells. If Cell A1 is blank AND cell A2 is also blank then cell A3 should have value of 1.00 with light grey color and if cell A1 is not blank AND cell A2 is not blank then the cell value of A3 should be 1.00 in black colour. How can I do this in Excel?

  43. i got it thank you soo much

  44. But the thing I want is instead of values if i keep letter or characters for which i want to change the color of whole row or colum

  45. Hi Svetlana

    Thank you for sharing knowledge it is more useful and easy for me by following your steps.

    Thanks alot

  46. Its very usefully for me

  47. In cell m145 I have formula =sum (a1:a5,b3:b7,c4:c9,etc...)

    I can not figure out how to click on m145 and command it to change all the cells in the formula to turn a certain color.

    Help please.

  48. Hello Svetlana - Thank you so much for offering your time to all of us...

    I want to change the background color of an entire row to the color associated with the results of the conditional formatting of a cell in the row.

    Thanks!

  49. Thanks very much for your helpful article.

  50. I want to change a range of cells based on the values of each equivalent adjacent one. For example.

    A1 0
    A2 1
    A3 1

    I want to change A1 only if its equivalent adjacent is 0, same rule for A2 and A3, get it?
    I want to apply this for a range of cells, because my spreadsheet is really huge and apply this conditional formatting for each cell is really painful.

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