Conditional formatting in Excel 2007 (based on value of another cell)

To format a cell based on the value of another cell follow these steps:

1. Select the cell you want to format, “A1″ in this example.
2. In the Home tab, Styles group, click on Conditional Formatting.
3. Select New Rule.

xl7cformat3

4. The New Formatting Rule window will open.

xl7cformat4

5. Click Use a formula to determine which cells to format.
6. The New Formatting Rule window will now look like this:

xl7cformat5

7. In the edit box labeled “Format values where this formula is true“, enter “=” followed by a formula referencing the other cell.

In this example the formula: “=B1<0″ is entered.
8. Click the Format button. The Format Cells window will open.

xl7cformat6

9. Specify the formatting you want. In this example I changed the cell color to “RED”.
10. Click OK to close The Format Cells window.

11. Click OK to close The New Formatting Rule window.

To summarize this example: cell A1 will have a RED color when cell B1 value is less than zero.

Comments

  1. nina says:

    Thank you so much! This works perfectly!!

  2. tony says:

    Hi,

    I’m having trouble using the conditional formatting. I’m trying to get an entered value to become red and negative based on the value of the cell to the left. Example…if A1=”ABC” then I want the value in B1 to change from 1,000 to (1,000)(in red). I have been trying to use the following formula: =if(a1=”3g”,b1*-1,”"). After I enter in the formula I changed the number format to be (1,234.00) (in red). It’s just not working out. Any assistance would be great!!

  3. admin says:

    To change color use the following formula as a conditional formatting rule and specify red fill:

    =A1=”3g”

    however you can’t change the value to negative using conditional formatting.

  4. Mohd Fairuz says:

    Hi, Guy.. I have a problem. Could you guide me how to change a cell content based on a cell colour. Example we have higlighted a column A with red colour and we want column B to be filled with text e.g OK. Could you guide me. Thank you

  5. admin says:

    This post is about formatting based on cells value.
    The only way to examine cell color is through VBA code (using macros).

  6. Raaj says:

    Hi, i need ur help urgently, i want to change the color of cell b1 when cell L1 contains word “babycare”
    kindly help. thanks

    • admin says:

      Use the procedure mentioned above and enter the following rule for cell B1:

      =L1=“babycare”

      Specify the fill color you want.

      Note: don’t copy the formula from the screen. Type directly instead.

  7. Jay says:

    Hi,

    I’m trying to format a cell, say B8, to change colors (red and green) based on the date located in another cell, say B13. I have tried the following formulas and have had no success:

    =$B$13=THISMONTH() to format green.
    =IF(B13=THISMONTH()) to format green.

    I have tried several others but have had little success. I am trying to format the cell to stay green if the date in the other cell is relative to the current month. If it is older then I would like the cell to turn red. Any assistance would be greatly appreciated!

    • admin says:

      Enter the following formula as a conditional formatting rule and give it a green color:

      =MONTH($B$13)=MONTH(TODAY())

      Then add this formula and give it a red color:

      =MONTH($B$13)<MONTH(TODAY())

      I hope this is what you are looking for.

  8. Aaron Ross says:

    Hey there. This should be a fun one for you. I have no problem basing formatting on a different cell E.G. if c4 is more than $1000.00. d4 should turn red. However, I want to be able to drag the formatting down for let’s say 50 cells. However, those cells keep referencing c4. Can you figure this on out?

    • admin says:

      Frankly I always have trouble with conditional formatting and autofill. But here is the easiest way I could think off:

      1. Conditionally format your first cell (D4 in your example).
      2. While D4 is selected, double click the format painter.
      3. Click on the other cells one at a time (selecting them all at once will not work).
      4. When done click on the format painter to deactivate it.
      This way the formulas should be updated correctly.

      I wish I had a better answer, but this is what I have right now. Any suggestions from our readers are welcome.

    • Jim Leeth says:

      Aaron
      This worked for me:

      1. Select the first cell in the column you are wanting to control with conditional fomatting, so D4.
      2. Open the conditional formatting to “manage rules”.
      3. Double click the formula and change it to look something like this “=C4>1000.00″
      4. Click okay and then change the “Applies to:” area to span the D column with something like this: “=$D$4:$D$100″ or more if you need more than row 100 to evaluate.
      5. Click Apply and I believe you will get what you were looking for.

  9. Jess says:

    I am trying to get the fill of a cell to change color based on a date and an extended period of time. example:

    Cell C3 contains 4/23/2008 i want the cell to be colored green if this date is 2 years past the date in the cell and colored red if it is 5 years past the date.

    The purpose is because something needs to be done within the 2 to 5 year range. Can you please help?

    Thanks

    • admin says:

      Sorry! Which cell you want to change color: C3 or the other cell? which one has the more recent date.

  10. Stan says:

    Hey Admin,

    Thanks for sharing your knowledge to us,

    I also got a same prob as what Aaron Ross had, I tried the 4 steps which you advised on May 12, 2011 at 11:47 am , but it is nt working out. The entire columns which I select are turning into the red color when I do a format painter, Can you help me out in this.

    • admin says:

      If you select the whole column one go you will get the same problem, because the cell reference in the formula will not change. You can verify that by selecting individual cells and checking the formula.
      Conditional formatting ==> Manage rules.

  11. Stan says:

    Not working out my friend !

    It is working in the first cell ie C3 = “Apple” ; D3 changes to Red, but I need this to happen till C500 atleast. Please help me on this.

  12. Stan says:

    Found it buddy….

    Set your conditional formatting on the first cell. Make sure that the formula doesn’t have the $ in the cell reference that your are comparing to. Excel will put the dollar signs in by default. Once you have the first cell set just select it and copy then select all the cells you want formatted the same and hit pastespecial and paste only the formats.

  13. benny says:

    Hi,

    I am trying to get the fill of a cell to change color based on it’s value. In example:

    if C3 is “Critical”, then I want it to be colored in red.
    if C3 is “High”, then I want it to be colored in green.
    if C3 is “Medium”, then I want it to be colored in yellow.

    But I want it to all the table.

    How can I do it ?

    • admin says:

      Use:
      1. Conditional formatting ==> Highlight cells rules ==> Equal to
      2. Enter “critical” in the left and select the color from the right.
      3. Repeat the process for the other values.

  14. Naresh Kushwa says:

    I want to swith between two colors as soon as value changes in column cell.
    Can any body help me….

    • admin says:

      I think the only way is to use VBA code.

      You need to use a worksheet event called “Worksheet Change”.

      1. Switch to visual basic editor by clicking Developer tab –> visual basic.

      2. Click on the name of your sheet in project explorer at your left.
      3. On the right pane select “Worksheet” from the left drop down and select “Change” from the right drop down.
      4. Now you are in the Worksheet_Change event.
      5. Add your code here..
      The following code will switch between green and blue color whenever a cell value change.

      'Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Interior.ColorIndex = 4 Then
      Target.Interior.ColorIndex = 5
      Else
      Target.Interior.ColorIndex = 4
      End If
      'End Sub

  15. Graham K says:

    Hi

    I’m trying to format a row from column A to column L to turn grey when cell H has a value of 100. The spreadsheet I am using will not change whether I select all the cells or one at a time other than cell H which changes to grey. On a new sheet it appears to only work on one cell at a time too.

    I have tried clearing all conditional formatting from the cells ( some are formated as date and others as numbers) with no result.

    Any suggestions?

  16. doug troeger says:

    i have a list of stocks with gains and losses. i would like to conditional format one shade if gain is 0 to 5% and a second shade if greater than 5%.

    =j8>0.05 shades the second shade. How do i do the first shade?
    =j8>0 and j8<0.05 isn't accepted.

    • admin says:

      1. Click conditional formatting —> Highlight Cells rules —> Between, then enter the values 0 and .05 and select the color you want.
      2. Click conditional formatting —> Highlight Cells rules —> Greater Than, then enter the value .05 and select the color.

  17. Ulysses Alexandre says:

    Hello.
    I would like your help, please.
    What happens is that I have a spreadsheet to control the state of the functionalities of a software engineering project, and for so I have columns for Priority, Implemented, Tested and Documented. I would like to change the complete row formatting based on the values inside these four columns. For example, for row 2, I want it to be light green when and only when the four mentioned columns have a value of “X” (meaing they are done). The same for every another row, because each row represents the state of a different functionality. Finally, I would like to make each row which has an ‘incomplete functionality’ to be red, meaning I still have to work on it.
    Many thanks for you, and if this was a bit hard to understand, please let me know how to turn it better wrote (sorry for bad english).

    Good luck for you all!

    • admin says:

      1. Suppose you are using cells A2, B2 C2 and D2 in row 2.
      2. Format all 4 cells to set a fill color of Red.
      3. Use the procedure in this post to set conditional formatting for each of the 4 cells with this formula:
      =AND(A2=”x”;B2=”x”;C2=”x”;D2=”x”). Set the fill color to Green.
      4. Repeat the procedure for other rows.

      Tested, works fine.

  18. Carl says:

    Hi. I have a work sheet of values that highlights automatically on certain values. I want to create a column next to that sheet with auto sum. What i need is that when one of the cells highlights (green) with the corresponding cell in the column next to it will fill with a value for instance Green = 250.00. Can you please help.
    Hope my question is clear enough?
    Thank in advance

    • admin says:

      You want to update the cell with a value based on the cell format and not the other way round as suggested by this post.
      Anyhow the only way to check for cell color, as far as I know, is through VBA code.

  19. Natasha says:

    Thanks for sharing this!

    I want to get a cell to change color, based on the values in the four cells beneath it. Each cell beneath it contains either an X or nothing in it. So, if there are at least two X’s in the four cells beneath it, then the top cell should be green. If the four cells beneath have only one X among all four cells, the top cell should be yellow. If there are no X’s, it should be red.

    Thanks!

    • admin says:

      Suppose
      the cell you want to format is B1, and the cells that contain the X’s are B2,
      B3, B4 and B5 then enter 3 rules for B1 and give them the fill colors as
      follows:

      =COUNTIF(B2:B5;”=X”)>=2 : Green
      =COUNTIF(B2:B5;”=X”)=1 : Yellow
      =COUNTIF(B2:B5;”=X”)=0 : Red

  20. Lynda says:

    I’m not sure if what I want to do is possible. If cell A1 = cell D1, then I want cell A1 to turn red. BUT I want it for the entire column. So A2 = D2, A2 will turn red; A3 = D3, A3 turns red and so on for the entire column. I know I could write the rule for every row…. but my sheet has 21,000 rows. So time would be an issue. Please if you could give me the formula that would do this would be greatly appreciated.

    • admin says:

      Do the formula for one row. Then check the discussion on this post ( a few comments above) on how to copy conditional formatting. There are different suggestions by different people.

  21. Tim Robinson says:

    I have a spreadsheet with about 12 columns in one section of it. I am trying to create a conditional formatting rule that will check each cell value in every row AND column. If the value in ANY cell is > cell value in column M of same row + 10 OR < same cell in column M – 10 then set the font to bold and red.

    I've tried this all sorts of ways and I also need to be able to copy/paste this to ever how many rows of data I have on this spreadsheet. The number of rows will almost always be inconsistent but the columns will always be the same and the value I'll be comparing the data in the first 9 columns/cells of a row will always be compared to the column M cell on the same row.

    Any ideas. This one is kicking my butt

    • admin says:

      Try this formula:

      =OR(B5>(M5+10);B5<(M5-10))

      Check the discussion on this post (a few comments above) on how to copy conditional formatting. There are different suggestions by different people.

  22. G says:

    Thanks for the great tips..

    I have a hard one I am working on.
    We have a colum that has customer numbers in it say C2
    I have them sorted by date and other criteria now I want to make easier to view with two colors
    The issue I run into is a customer may have several rows and I want them to all to be gray.

    Example

    C2 1233321 – White
    D2 1254342 – Gray
    E2 1254342 – Gray
    F2 1236363 – White
    G2 1236363 – White
    H2 1233321 – Gray

    I can not seem to figure out the correct method. Any thoughts would be greatly appreciated.

    • admin says:

      1. Select all cells that have your customer numbers.
      2. Use : Conditional formatting ==> Highlight cells rules ==> Equal to.
      3. Enter customer number in the left and select the color from the right (if the color you want is not available, select custom format at the bottom of the list).
      4. Repeat the process for all customers.
      This is not practical if you have many customers (I mean unique numbers), but since you want to base colors on customers then you have to do it this way.

  23. K says:

    Ok here is my problem….. In excel 2007 i have a column with due dates but what I want it to do is when the due date is 7 days away I want it to turn yellow and when it has becomes and/or is passed the due date I want it to turn red please help me

    • admin says:

      Suppose your date is in cell A1, insert 2 rules for the cell:

      =(A1-TODAY())>7 make fill color red
      =(A1-TODAY())=7 make fill color yellow

      Apply the same rules for other date cells.

      • K says:

        I am really new to this so could you give me step by step instructions and my due date column starts in F4 and ends F50. Please tell me what to do from when the spread sheet is open until the last format is in and exactly what to type, you know excel for dummies

  24. admin says:

    I am sorry but I think this post has enough details. It won’t be practical to elaborate more than that.

  25. san says:

    hi i want to change the colour of the cell according to the value .. how to change it?

  26. Bonnie says:

    You may have already answered this, but I am still lost. What I need to do is create a way to change the color of a production sheet based on whether or not a daily quota has been met. But there is the possibility that the number for the quota may change. So I need, for example, if E9 is greater than or equal to the value of C9, then the cell turns green, but if E9 is between 1 and the value of C9, then it turns red. I need the cell to stay blank when there is no data has been added yet. Please help!!!

    • Bonnie says:

      I also need E10 through E38 to have the same value, and all reference C9. I thought I had it, but it only seems to work for just C9 and E9, but I need for say E15 to also turn red when between 1 and the value of C9 and turn green when greater than or equal to the value of C9. Instead, it turns red when between 1 and the value of C15, instead of C9.

      • admin says:

        Enter two rules for E9 as follows:

        =E9>=$C$9 —————-> green color
        =AND(E9< $C$9;E9>0) ————-> red color

        Notice the $ sign in case of C9.

  27. Paul H says:

    Hi,

    You are awesome. I hope you can answer this too. I have a sheet where I want to show blank cells, but only if colomn a is populated.

    So if A1 is blank, Cells B2 and C2 = no colour
    but
    if A1 is populated, B2 and C2 = Blue (only if they are blank)

    I hope you understand. Then I also want that formula to cover the whole column.

    :-)

    Thank you

    Paul H

    • admin says:

      Thank you.

      1. Highlight the entire column B (click on the column header).
      2. Follow the steps in this post to add the following rule and specify BLUE color:

      =AND(A1>" ";B1< =" ")

      3. In this rule each cell in column B will refer to the adjacent cell in column A. e.g. B1 --> A1, B2 --> A2 and so on.
      4. If you want to refer to A1 all the time then enter $A$1 in the formula instead of A1.

      Apply the same procedure for column C.

      Note: there is no space between "<" and "=" in the formula.

      • Paul H says:

        Hi,

        This didnt work. I got a syntax error for using the “;” so I changed it to a “,”. This still did not work.

        I will continue to try variations on the AND.

        So just to clarify, if A = “” then nothing happens, if A=”" and B is blank it turns blue, if A = “” and B=”" then nothing.

        Thank you.

      • Paul H says:

        Ok, done it. Thank you

        =AND(A1″”,B1=”")

        Cheers.

  28. Deltaray says:

    Hi,

    Would you mind helping me with this excel question please?

    I am recording my App downloads on a spreadsheet and would like the cell i’m typing into to either turn green if the value is higher than the previous cell or red if lower than the previous cell. Is this possible?

    Many thanks.

    • admin says:

      I am not sure what you mean by the previous cell, but I will assume that your current cell is A2 and your previous cell is A1 then you can try this:
      Suppose your range is A1 to A15,
      1. Select A2 to A15.
      2. Add a new rule =A2>A1 give it a green color
      3. Add another rule =A2<A1 give it a red color

      • Deltaray says:

        Thanks Admin! Let me be clear as i think it needs more explanation. I have differing totals in A1,A2, A3 etc…probably all the way to A365 for a years example. Going from A1 down to A2 and then incorporating every cell thereafter i need each cell to be red or green depending on what the last total was. So…if A1 was 2 and then A2 was 6 i’d need A2′s cell to be Green but if A3 was 4 i’d need that to be red and so on…

      • admin says:

        This was my assumption in fact. So the formulas I mentioned in the previous answer are still valid.
        Regards.

  29. Shalini says:

    Hi , This is an awesome site and the respones are awesom.I needed some help on conditional formatting with icon sets.I have 2 data sets.One with stocks ordered adn another with stock available.I would like icon sets to represent the comparison.If stock ordered if > stock available the red arrow should display and the other way round green.If they are equal a yellow arrow maybe displayed.I have done the same thing with defining a new rule and formatting the fill with the color but it looks more professional with the arrows.Is this possible?

    • admin says:

      Icon sets are used to classify a range of cells into different categories depending on their values. Unfortunately they are not available when you want to format a cell depending on the value of another cell (using a formula).

  30. Tony S says:

    What formula do I use to refer to another cel and enter a condition between two numbers e.g.
    if cell D12 >0 but<5
    or
    if cell D12 is between 0 and 5

    • admin says:

      Use this formula:

      =AND(D12>0;D12<5)

      N.B. I use ";" as a separator. You may have to use "," depending on your installation.

  31. Jason H says:

    I have read through a bunch of these, but none of them are helping me out. I want a cell to change color based on the relation of the date of that cell to the date I enter in the next one.

    For example:
    A1 is 7/20/2011 and B1 is 7/21/2011. I want A1 to turn red because it is a younger date.
    A1 is 7/20/2011 and B1 is 7/19/2011. I want A1 to turn green because it is less than or equal to A1.

    Think you can help???

    • admin says:

      Select your range then enter these two rules for cell A1:

      =(A1-B1)<0 give this rule red color
      =(A1-B1)>0 give this rule green color

      Your fields must be formatted as dates.

  32. Debra says:

    Wow, this site is so helpful. I’m hoping you can help me with my conditional formatting problem, as I have read through the comments, and I didn’t see a similar situation.

    I want to compare a date in A1 (May-10) to a date in B1 (Feb-09), and then I want to compare A1 to today’s date. Basically, I only want to format A1 as red if A1 is more recent than B1 AND it is older than one year.

    I will eventually want to format with a range of colors to include 90, 180 and 270 day increments as well.

    Any help would be much appreciated!

  33. admin says:

    Create a rule for cell A1 using this formula:

    =AND(A1>B1;TODAY()-A1>365)

  34. Nat says:

    Hi there,

    I’m hoping you can guide me through some conditional formatting…

    I’ve created a worksheet that is going to be partially populated by myself and then the balance of the data will be filled in by others. I’d like them to enter a value for O2 if the value of I2 is one of the following: 7,8,9,10,11,DM,SM,ASM,CD,CM,MR

    Any insight on how this can be achieved would be sincerely appreciated.

    Thanks

  35. ian says:

    Hi admin,

    I think you can help on this problem.
    I need to know how to conditionally format cells based on the value/text from another cell. I want to automatically input the text “visited” in the cell B1 if there is a date input in A1. However, if there is no date, i wanted the cells in column B as blank…Your help is much appreciated.
    Thank you very much.

    • admin says:

      No need for conditional formatting. Just enter the following formula in B1:
      =IF(ISBLANK(A1);" ";"Visited")
      Unfortunately there is no function in Excel to verify if a cell has a date. This formula will just check if A1 has a value or not, you have to make sure it has a valid date.

  36. ian says:

    the semicolon didnt work, but when i changed it comma, it worked… but nonetheless, thank you very much admin! God bless you!

  37. Janyne says:

    Hi

    I was just wondering how do you conditional format following: The cell D1 must be red if the cell B1 contains EL4 this is only part of the word so I cannot say equal to. Sorry to bother you.

  38. Glen says:

    I have a column let’s say E that contains either a “V” or a “P” and have another column let’s say M that contains number between 0 and 100. What I want to do is if column E contains a V and in the corresponding row column M contains a number between 10 and 20 I want the cell in column M to highlight in blue.

    • admin says:

      Click on cell M1 and highlight the range you want in column M then add a new rule for cell M1 (with blue fill) as follows:

      =AND(E1="V";AND(M1>9;M1<21))

      Replace the ";" with "," if required.

  39. ian says:

    hi admin,

    i need to have multiple if function based on multiple conditions from different cells. for example, i wanted to automatically put texts either “visited”, or “for site visit” or “NVN” on cell C1. the Cell in C1 is dependent on B1 and A1. For example if A1 has a value or text in it, then C1 must automatically have “NVN”, but if A1 has no value, then the cell reference must be B1, if B1 has a value, then it must be “visited” but if it has no value, then it must be-”for site visit” .

    by the way, i would prefer not to use the syntax ifblank, can this be done? thanks for your help…

    • admin says:

      I am sorry but there is no formatting involved here. Please try the “IF” function or other logical functions instead.

  40. Nick L says:

    Hello-hoping i can please get some guidance on the below; thanks in advance.

    I would like to set up conditional formatting in column Q based on the value in Q and the value in O.

    Column O has one of four values (10,11,20,21)
    Column Q can range from -50 to 50

    If O=10 or 11 and Q>-6 then red……Q=-6 then yellow……..Q-4 then red……Q=-4 then yellow……..Q<-4 then green

  41. JC Nhan says:

    Does anybody know how to format Canada Postal Code as ‘ALL CAPS and SPACE IN BETWEEN’ the postal code? Example: T9M 2C6. Thank you very much.

    • admin says:

      Please check other posts that handle text functions. Nothing to do with conditional formatting here..

  42. Sky says:

    Is there a way to format a spreadsheet where for instace the value in the Column A are Less than the value in Column B then the value in Colum A would be highlighted? I am trying to create a inventory sheet for our warehouse. Column A has how much of a product we actually have in stock and Colomn B has the minimum amount we must have in stock. Therefore if Column A drops below value in Column B it will be highlighted, showing us we need to build our inventory of that part.

    Thanks

    • admin says:

      Select the first cell in column A let us say A1 , highlight your range in column A then create a rule for cell A1 as follows:

      =A1 then set the formatting you want to highlight the cell.