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

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

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

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

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

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

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

  7. thanks i problem solved

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

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

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

  11. Hi,

    yet, I don't example for :

    easy coloring backgroud of cells for chosen values in cells

    For example in the row I have values 0 - 100 - in 101 columns

    I chose values : e.g. 37, 56, 100 and I want these cells
    with chosen backgroud color. For example green.

    Thank you for help

  12. Hi,

    Is there a way to format the cells to change from one colour to another depending on a word added to the cell. I want to add "anyword" meaning any word added to the cell will change the colour of the cell.

    Thanks,

  13. Practical.

    thank you.

  14. If the Cell A1 is Colour than its - 1
    If the cell A1 is Blank than its - 2

  15. hi,
    Need Your help..
    I have problem of high light lowest value from diff.Columns in single row
    e.g. i want to do find lowest value from e6:h6 and then highlight with red colur & bold it.

  16. Is it possible to fill up text for the value of cell?? Means if the value of cell is 100, it format text as "Below average". Is it possible?

  17. Thank you very much.....................

  18. Hi Svetlana Cheusheva,

    How the color of the cell will be changed automatically to the same color of a cell already changed via conditional formatting?

  19. Great, Great,Great..
    Very useful article.
    i got much help from here.
    Thanks alot..

  20. I want to change the color of a cell on one tab and have it automatically change the color of a cell on a different tab. How can I do this? Both cells will have the same text, but be in a different place. I know how to copy data from one sheet to another. But I don't know how to copy the cell color too.

  21. Congratz. This is very helpful.

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

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

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

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

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

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

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

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

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

  31. thanks a lot

  32. thanks alot, for the information

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

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

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

  36. Dear sevetlana,

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

    Ajay

  37. 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 ;-)

  38. clear n precise...very very useful

    Thanks a ton

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

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

  41. Thank you very much.

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

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

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

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

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

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

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

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

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

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