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.

Everyone knows that changing the background color of a single cell or a range of data in Excel is easy as clicking the "Fill color" Fill color icon button . But what if you want to change the background color of all cells with a certain value? Moreover, what if you want the background color to change automatically along with the cell value's changes? Further in this article you will find answers to these questions and learn a couple of useful tips that will help you choose the right method for each particular task.

How to change a cell's color based on value in Excel dynamically

The background color will change dependent on the cell's value.

Task: You have a table or range of data, and you want to change the background color of cells based on cell values. Also, you want the color to change dynamically reflecting the data changes.

Solution: You need to use Excel conditional formatting to highlight the values greater than X, less than Y or between X and Y.

Suppose you have a list of gasoline prices in different states and you want the prices greater than USD 3.7 to be of the color red and equal to or less than USD 3.45 to be of the color green.
A table listing gasoline prices in different states

Note: The screenshots for this example were captured in Excel 2010, however the buttons, dialogs and settings are the same or nearly the same in Excel 2016 and Excel 2013.

Okay, here is what you do step-by-step:

  1. Select the table or range where you want to change the background color of cells. In this example, we've selected $B$2:$H$10 (the column names and the first column listing the state names are excluded from the selection).
  2. Navigate to the Home tab, Styles group, and choose Conditional Formatting > New Rule….
    On the Home tab, in the Styles group, click Conditional Formatting - New Rule….
  3. In the New Formatting Rule dialog box, select "Format only cells that contain" under "Select a Rule Type" box in the upper part of the dialog box.
  4. In the lower part of the dialog box under "Format Only Cells with section", set the rule conditions. We choose to format only cells with a Cell Value - greater than - 3.7, as you can see in the screenshot below.
    Select 'Format only cells that contain' and set the rule conditions.

    Then click the Format… button to choose what background color to apply when the above condition is met.

  5. In the Format Cells dialog box, switch to the Fill tab and select the color of your choice, the reddish color in our case, and click OK.
    In the Format Cells dialog box, on the Fill tab, select the background color of your choice.
  6. Now you are back to the New Formatting Rule window and the preview of your format changes is displayed in the Preview box. If everything is Okay, click the OK button.
    The preview of format changes is displayed in the Preview box.

    The result of your formatting will look similar to this:
    The background color of selected cells is changed based on cell values.

    Since we need to apply one more condition, i.e. change the background of cells with values equal to or less than 3.45 to the green color, click the New Rule button again and repeat steps 3 - 6 setting the required condition. Here is the Preview of our second conditional formatting rule:
    A rule to change the background of cells with values equal to or less than 3.45 to the green color

    When you are done, click the OK button. What you have now is a nicely formatted table that lets you see the highest and lowest gas prices across different states at a glance. Lucky they are in Texas :)
    The background color is changed based on 2 conditional formatting rules.

    Tip: You can use the same method to change the font color based on the cell's value. To do this, simply switch to the Font tab in the Format Cells dialog box that we discussed in step 5 and choose your preferred font color.

    The font color is changed based on 2 conditional formatting rules.

How to permanently change a cell's color based on its current value

Once set, the background color will not change no matter how the cell's contents might change in the future.

Task: You want to color a cell based on its current value and wish the background color to remain the same even when the cell value's changes.

Solution: Find all cells with a certain value or values using Excel's Find All function or Select Special Cells add-in, and then change the format of found cells using the Format Cells feature.

This is one of those rare tasks that are not covered in Excel help files, forums and blogs and for which there is no straightforward solution. And this is understandable, because this task is not typical. And still, if you need to change the background color of cells statically i.e. once and forever unless you change it manually again, proceed with the following steps.

Find and select all cells that meet a certain condition

There may be several possible scenarios depending on what kind of values you are looking for.

If you need to color cells with a particular value, e.g. 50, 100 or 3.4, go to the Home tab, Editing group, and click Find Select > Find….
Go to the Home tab, Editing group, and click Find Select > Find…

Enter the needed values and click the Find All button.
Enter the needed values and click the Find All button.

Tip: Click the Options button in the right-hand part of the Find and Replace dialog to get a number of advanced search options, such as "Match Case" and "Match entire cell content". You can use wildcard characters, such as an asterisk (*) to find any string of characters or a question mark (?) to find any single character.

In our previous example, if we needed to find all gas prices between 3.7 and 3.799, we would specify the following search criteria:
Use the wildcard character to find all gas prices between 3.7 and 3.799

Now select any of the found items in the lower part of the Find and Replace dialog window by clicking on it and then press Ctrl + A to select all found entries. After that click the Close button.
Select all found items and click the Close button.

This is how you select all cells with a certain value(s) using the Find All function in Excel.

However, what we actually need is to find all gas prices higher than 3.7 and regrettably Excel's Find and Replace dialog does not allow for such things.

Luckily, there is another tool that can handle such complex conditions. The Select Special Cells add-in lets you find all values in a specified range, e.g. between -1 and 45, get the maximum / minimum value in a column, row or range, find cells by font color, fill color and much more.
Select Special Cells add-in for Excel

You click the Select by Value button on the ribbon and then specify your search criteria on the add-in's pane, in our example we are looking for values greater than 3.7. Click the Select button and in a second you will have a result like this:
Select all values within a particular range using Select Special Cells add-in.

If you are interested to try the Select Special Cells add-in, you can download an evaluation version here.

Change the background color of selected cells using "Format Cells" dialog

Now that all cells with a specified value or values are selected (either by using Excel's Find and Replace or Select Special Cells add-in) what is left for you to do is force the background color of selected cells to change when a value changes.

Open the Format Cells dialog by pressing Ctrl + 1 (you can also right click any of selected cells and choose "Format Cells…" from the pop-up menu, or go to Home tab > Cells group > Format > Format Cells…) and make all format changes you want. We will choose to change the background color in orange this time, just for a change :)
Change the background color of selected cells using the Format Cells dialog.

If you want to alter the background color only without any other format changes, then you can simply click the Fill color button and choose the color to your liking.
Change the background color of selected cells by clicking the Fill color button.

Here is the result of our format changes in Excel:
The backgrounds color of selected cells is changed permanently, regardless of the cell value's changes.

Unlike the previous technique with conditional formatting, the background color set in this way will never change again without your notice, no matter how the values change.

Change background color for special cells (blanks, with formula errors)

Like in the previous example, you can change the background color of special cells in two ways, dynamically and statically.

Use Excel formula to change background color of special cells

A cell's color will change automatically based on the cell's value.

This method provides a solution that you will most likely need in 99% of cases, i.e. the background color of cells will change according to the conditions you set.

We are going to use the gas prices table again as an example, but this time a couple of more states are included and some cells are empty. See how you can detect those blank cells and change their background color.

  1. On the Home tab, in the Styles group, click Conditional Formatting > New Rule… (see step 2 of How to dynamically change a cell color based on value for step-by-step guidance).
  2. In the "New Formatting Rule" dialog, select the option "Use a formula to determine which cells to format". Then enter one of the following formulas in the "Format values where this formula is true" field:
    • =IsBlank()- to change the background color of blank cells.
    • =IsError() - to change the background color of cells with formulas that return errors.

    Since we are interested in changing the color of empty cells, enter the formula =IsBlank(), then place the cursor between parentheses and click the Collapse Dialog button Collapse Dialog icon in the right-hand part of the window to select a range of cells, or you can type the range manually, e.g. =IsBlank(B2:H12).
    Enter the formula and select a range of cells.

  3. Click the Format… button and choose the needed background color on the Fill tab (for detailed instructions, see step 5 of "How to dynamically change a cell color based on value") and then click OK.

    The preview of your conditional formatting rule will look similar to this:
    A rule to change the background color of blank cells using a formula

  4. If you are happy with the color, click the OK button and you'll see the changes immediately applied to your table.
    Click the OK button and you'll see the changes immediately applied to your table.

Change the background color of special cells statically

Once changed, the background color will remain the same, regardless of the cell values' changes.

If you want to change the color of blank cells or cells with formula errors permanently, follow this way.

  1. Select your table or a range and press F5 to open the "Go To" dialog, and then click the "Special…" button.
    Open the
  2. In the "Go to Special" dialog box, check the Blanks radio button to select all empty cells.
    Check the Blanks radio button to select all empty cells.

    If you want to highlight cells containing formulas with errors, choose Formulas > Errors. As you can see in the screenshot above, a handful of other options are available to you.

  3. And finally, change the background of selected cells, or make any other format customizations using the "Format Cells" dialog as described in Changing the background of selected cells.

Just remember that formatting changes made in this way will persist even if your blank cells get filled with data or formula errors are corrected. Of course, it's hard to imagine off the top of the head why someone may want to have it this way, may be just for historical purposes :)

How to get most of Excel and make challenging tasks easy

As an active user of Microsoft Excel, you know that it has plenty of features. Some of them we know and love, others are a complete mystery for an average user and various blogs, including this one, are trying to shed at least some light on them. But! There are a few very common tasks that all of us have to perform daily and Excel simply does not provide any features or tools to automate them or make an inch easier.

For example, if you need to check 2 worksheets for duplicates or merge rows from single or different spreadsheets, it would take a bunch of arcane formulas or macros and still there is no guarantee you would get the accurate results.

That was the reason why a team of our best Excel developers designed and created 60+ add-ins that we call the Ultimate Suite for Excel. These smart tools handle the most grueling, painstaking and error-prone tasks in Excel and ensure quickly, neatly and flawless results. Below is a short list of just some of the tasks the add-ins can help you with:

Just try these add-ins and you will see that your Excel productivity will increase up to 50%, at the very least!

If you like the tools, please do come back and use this special offer:

Promo Code for Ultimate Suite - exclusive offer for our blog readers!

That's all for now. In my next article we will continue to explore this topic further and you will see how you can quickly change the background color of a row based on a cell value. Hope to see you on our blog next week!

369 responses to "Two ways to change background color in Excel based on cell value"

  1. Perry says:

    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.

  2. Claire says:

    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.

  3. mh12 says:

    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!

  4. Clement Paul says:

    It was very helpful....thank you

  5. Kristy Jones says:

    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!

  6. Aamir Saleem says:

    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

  7. Sagar says:

    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.

  8. nitin says:

    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

  9. varun says:

    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. Vishwan says:

    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?

    • Vishwan,

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

      • Ganesh says:

        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.

  11. Abdullah says:

    Thanks for this helpful article.

  12. JB says:

    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.

  13. vishwas says:

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

    Thanks

  14. Dillip Rout says:

    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.

  15. Palani K says:

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

    • Deepak Bajaj says:

      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.

  16. Lokesh says:

    Useful information.

    Thanks

  17. Sushant says:

    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.

    • Deepak Bajaj says:

      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.

  18. Dinesh says:

    Hi All,

    The numbers "Age" column should be filled in with colors against the status mentioned against each of them.

    How can we do this using conditional formatting.
    Say for example cell containing "3" should be filled with Orange color.

    Age Status
    3 Orange
    4 Green
    10 Red

    • Deepak Bajaj says:

      Hello Dinesh

      Here is your solution step by step

      let me assume cell A2=3, A3=4 and A4=10

      1. Go to cell B2
      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
      =A2=3
      7. Click on format select fill and select the color you want
      8. Click ok again ok and again ok
      9. Go to Cell B3 and repeat step 2 to step 5
      10. In the box of "Format values where this formula is true:" type =A3=4
      11. Repeat step 7 and step 8
      12. Go to Cell B4 and repeat step 2 to step 5
      13. In the box of "Format values where this formula is true:" type =A4=10
      14. Repeat step 7 and step 8

      its done.

      enjoy.

  19. Robbie says:

    Can I format a single cell or set of cells to highlight if another single cell or set of cells has any value? In other words, I have 5 cells that will contain data. If cells further down change from blank to any value (could be numeric or alpha or combo) I want the 5 cells with data to then be highlighted. Is that possible??

  20. SACHIN says:

    superb .....its work.
    thanks.

  21. Will says:

    Hi Svetlana,

    What I am trying to achieve is a reporting tool.
    I want to have default values and when they change, then have the cell display as red.

    So the scenario is A1 = Yes, B1 = No
    When the user changes either one, I'd want it to change colour. Is this done through conditional formatting?

  22. Mohan Kumar says:

    Hi,

    I want the following thing

    If B2 is equal to D2 then the cells become Green

    If B2 is equal to C2 then the cells become Red

    Can you please tell me

  23. Qasim (Pakistan) says:

    This is great. You have explained it so easily. Thanks

  24. LakshmiNarayana says:

    Hi All,
    I need help in excell my question is if i change a color in one cell of sheet
    automatically it should change in another cell of another sheet.

    Example:I have 6 sheets with same information.
    In "Sheet1 Jan sales" "Sheet2 FEBsales".If I change color "Yellow" For "Sheet1 Jan sales"in A6 cell "YEs" it should same refelect in "Sheet2 FEBsales" in A6 cell "Yes" as Yellow.

    Please send me with temp let with clear explanation.

    Waiting for solution for my question
    regards,
    Lakshmi

  25. Ganesh says:

    Thnx 4 the HELP......

  26. Scott Hayfield says:

    Hello. I am not 100% whether it has already been answered, so I'm sorry if the question is being repeated.

    I want to be able to change a cell (i.e. someone's name to a different colour) if they have responded to me. Basically if they have not response cell is blank, I want the font colour of the persons name to be Red, and if they have responded and the response cell has some data in it, I want their name to become Green. How would I do this?

    I hope that's clear enough? If not I can provide a clearer question.

    Thankyou! :)

  27. Diego says:

    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!

  28. Rashmi says:

    Thanks very much for your helpful article.

  29. Neil says:

    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!

  30. Sonya says:

    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.

  31. Vijay Kshirsagar says:

    Its very usefully for me

  32. Mohammad Afroz says:

    Hi Svetlana

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

    Thanks alot

  33. Mohammad Afroz says:

    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

  34. Mohammad Afroz says:

    i got it thank you soo much

  35. Fayyaz says:

    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?

  36. Khokon says:

    Useful Article.Thanks...

  37. HAZEM says:

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

  38. Ashfak says:

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

  39. Dharmesh says:

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

  40. Shawn says:

    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

  41. Hafez says:

    Many Thanks Easy to understand.

  42. Yudhi Setiawan says:

    This help me. Thank you

  43. Zakir Hussain says:

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

  44. GNANASEKARAN says:

    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

  45. Faizan ahamad says:

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

  46. Dipdatt M. Patil says:

    Thanks,

    It is really useful for us ...

    Thanks again

  47. Stephanie says:

    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

  48. Shakeel says:

    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

  49. prakash says:

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

  50. prakash says:

    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

  51. Conditional Formatting says:

    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?

  52. brian says:

    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!

  53. roonie says:

    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 .

  54. Sathyamoorthy.K says:

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

  55. Ratheesh says:

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

  56. Sobir says:

    Thank you very much.

  57. Subhash says:

    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

  58. Naung says:

    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!

  59. speedy gonzales says:

    clear n precise...very very useful

    Thanks a ton

  60. Martin says:

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

  61. Ajay Choudhary says:

    Dear sevetlana,

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

    Ajay

  62. mukesh says:

    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.

  63. Brent says:

    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?

  64. Deepika K. says:

    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.

  65. ismail khan says:

    thanks alot, for the information

  66. kartheek says:

    thanks a lot

  67. FK says:

    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

  68. Hassan Ali says:

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

  69. Rahul says:

    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

  70. Shaz says:

    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

    • TychaBrahe says:

      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?

  71. neethu benny says:

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

  72. lekhraj says:

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

  73. Ali Yasir says:

    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?

  74. Roshan kumatr says:

    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

  75. Roshan kumatr says:

    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

  76. Ivek says:

    Congratz. This is very helpful.

  77. MS says:

    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.

  78. Fahad Ali Khan says:

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

  79. muzafar says:

    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?

  80. qqkk says:

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

  81. Suraj Bista says:

    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?

  82. Devmurari Kalpesh says:

    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.

  83. Vikas Salvi says:

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

  84. Amir says:

    Practical.

    thank you.

  85. Waji says:

    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,

  86. John says:

    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

  87. MJ says:

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

  88. Ram says:

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

  89. Malcolm says:

    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

  90. Rahul says:

    thanks i problem solved

  91. Maged Metry says:

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

  92. rohit says:

    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.

  93. JIMMY says:

    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.

  94. Harshal Thakare says:

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

  95. Gavin says:

    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?

  96. Manoj says:

    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.

  97. vimal says:

    thnx for all of these

  98. Desiree says:

    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!

  99. Adam says:

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

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