• Conditional Formatting (Excel 2K)

    Author
    Topic
    #401877

    Sorry, I know this is simple but I just can’t get it right, I’ve done a search and can’t find the answer and my copy of “Special Edition Using Office” has not been returned!

    I have two columns of figures: column Q and column W.
    If the result in W2 is greater than Q2, I want the text to be green: if less than Q2, the text must be red.
    I can achieve that in conditional formatting easily enough, but can’t work out how to apply that conditional formatting to the *whole* of column W.
    At the moment, all i can get is for every cell in W to compare itself to Q2, rather than its relative partner.
    In other words, I need W16 to show whether it’s greater or smaller than Q16.
    Many thanks for any help
    Jim

    Viewing 3 reply threads
    Author
    Replies
    • #795452

      Method 1:
      – Select all cells in column Q that you want to apply conditional formatting to, say Q1:Q100.
      – Select Format | Conditional Formatting…
      – Set up the conditional formatting for the active cell in the selected range (probably the first cell)
      – This is essential: make sure that the reference to the cell in column W is relative; the default is to make it absolute by including $ characters in the cell reference; remove these $ characters: =W1 instead of =$W$1.
      – Click OK. Since the cell reference in conditional formatting is relative, it will adapt itself for the other cells.

      Method 2:
      – Select the first cell in column Q that you want to format, say cell Q1.
      – Select Format | Conditional Formatting…
      – Set up conditional formatting, and as above, make sure that the reference to the cell in column W is relative: =W1 instead of =$W$1.
      – Click OK.
      – Click the Format Painter button on the toolbar.
      – Select the other cells in column Q that you want to format.

      • #795460

        Sorry Hans, I replied to my own post rather than yours.
        Jim

      • #795461

        Sorry Hans, I replied to my own post rather than yours.
        Jim

      • #795670

        I thought for sure that Method 2 was going to be just fill the cells in col Q after conditionally formatting Q1. Maybe that’s method 3?

        Fred

        • #795759

          If you mean fill down, that would work if Q1 contains a formula that can be filled down. Otherwise, it won’t work.

          • #795795

            (Edited by fburg on 07-Mar-04 16:32. lined up col Q and W letters)

            Hans,

            Not sure about Excel 2K. But it worked in XP. Maybe my terminology was a bit loose.

            I did a quick experiment before posting but didn’t keep the workbook. Here’s what I did:

            ….Q W
            1| 7 3
            2| 2 3
            3| 6 3

            edited above table to line up col letters

            All of the above are constants. Selected W1 (or was it Q1 – doesn’t matter now, I don’t think). Went into Conditional Format. Selected “Cell Value Is” with condition “Less than” and Value =Q1 by clicking on Q1. Of course, I got =$Q$1 as you had indicated. I deleted the dollar signs. Went into the format and selected a font color. Similar for “greater than or equal” condition.

            I then did a right drag of the fill handle for W1 down to W3 and chose “Fill Formatting Only”. I checked the conditional formats of W2 and W3 and they were the same conditions as for W1 except they referred to the corresponding cells in Q.

            I also just tried a copy of W1 and a Paste Special / Formats and this worked as well.

            I would guess that if there are some strange formatting in W1 that’s different than the formatting in W2, W3, … (eg, maybe the borders or cell fill), then my approaches wouldn’t work in that the cell formatting would be copied as well. But I think that would hold true for the Format Painter as well. It was unclear from the original post if this issue was a real problem.

            Fred

            • #795815

              Fred,

              Yes, the methods you describe will work just as well as the format painter. From your first reply it wasn’t clear to me that you meant dragging the fill handle with the right mouse button.

            • #795816

              Fred,

              Yes, the methods you describe will work just as well as the format painter. From your first reply it wasn’t clear to me that you meant dragging the fill handle with the right mouse button.

            • #795851

              Hans,

              I could say I’m a lefty confused in this right-handed world. confused But I’m right handed and I did mean to right drag. Usually we don’t think of right dragging the fill handle – at least I don’t. Don’t know what possessed me to do it for this question.

              I also take it from your response that the behavior I described exists in Excel 2K.

              So now we have 4 methods!!! Leave it to MS to confuse people even more by providing umpteen ways of doing the same thing (umpteen >=4 in this case).

              Fred

            • #795852

              Hans,

              I could say I’m a lefty confused in this right-handed world. confused But I’m right handed and I did mean to right drag. Usually we don’t think of right dragging the fill handle – at least I don’t. Don’t know what possessed me to do it for this question.

              I also take it from your response that the behavior I described exists in Excel 2K.

              So now we have 4 methods!!! Leave it to MS to confuse people even more by providing umpteen ways of doing the same thing (umpteen >=4 in this case).

              Fred

          • #795796

            (Edited by fburg on 07-Mar-04 16:32. lined up col Q and W letters)

            Hans,

            Not sure about Excel 2K. But it worked in XP. Maybe my terminology was a bit loose.

            I did a quick experiment before posting but didn’t keep the workbook. Here’s what I did:

            ….Q W
            1| 7 3
            2| 2 3
            3| 6 3

            edited above table to line up col letters

            All of the above are constants. Selected W1 (or was it Q1 – doesn’t matter now, I don’t think). Went into Conditional Format. Selected “Cell Value Is” with condition “Less than” and Value =Q1 by clicking on Q1. Of course, I got =$Q$1 as you had indicated. I deleted the dollar signs. Went into the format and selected a font color. Similar for “greater than or equal” condition.

            I then did a right drag of the fill handle for W1 down to W3 and chose “Fill Formatting Only”. I checked the conditional formats of W2 and W3 and they were the same conditions as for W1 except they referred to the corresponding cells in Q.

            I also just tried a copy of W1 and a Paste Special / Formats and this worked as well.

            I would guess that if there are some strange formatting in W1 that’s different than the formatting in W2, W3, … (eg, maybe the borders or cell fill), then my approaches wouldn’t work in that the cell formatting would be copied as well. But I think that would hold true for the Format Painter as well. It was unclear from the original post if this issue was a real problem.

            Fred

        • #795760

          If you mean fill down, that would work if Q1 contains a formula that can be filled down. Otherwise, it won’t work.

      • #795671

        I thought for sure that Method 2 was going to be just fill the cells in col Q after conditionally formatting Q1. Maybe that’s method 3?

        Fred

      • #795674

        Hi Hans

        Just curiousity here… I’m familiar with relative and absolute references – the W1 and $W$1 example makes perfect sense. But what would be the effect of using $W1 or W$1. That is, making either the column or the row absolute and the other relative?

        Alan

        • #795676

          It will keep that reference relative.

          If you select a1:C10
          cond-format
          =$E$1 will be true only for those cells that are =E1 (all compared to same cell)
          =E1 will be true only for those cells that relative to E1 A1-E1, A2-E2, B1-F1, C1-G1, etc (all compared to same row as cell but 4 cols over)
          =$E1 will be true only for those cells that relative to $E1 A1-E1, A2-E2, B1-E1, C1-E1, etc (all compared to same col (E), but same row as cell)
          =E$1 will be true only for those cells that relative to E$1 A1-E1, A2-E1, B2-F1, C2-G1, etc (all compared to same row (1), but 4 cols over)

          Steve

        • #795677

          It will keep that reference relative.

          If you select a1:C10
          cond-format
          =$E$1 will be true only for those cells that are =E1 (all compared to same cell)
          =E1 will be true only for those cells that relative to E1 A1-E1, A2-E2, B1-F1, C1-G1, etc (all compared to same row as cell but 4 cols over)
          =$E1 will be true only for those cells that relative to $E1 A1-E1, A2-E2, B1-E1, C1-E1, etc (all compared to same col (E), but same row as cell)
          =E$1 will be true only for those cells that relative to E$1 A1-E1, A2-E1, B2-F1, C2-G1, etc (all compared to same row (1), but 4 cols over)

          Steve

      • #795675

        Hi Hans

        Just curiousity here… I’m familiar with relative and absolute references – the W1 and $W$1 example makes perfect sense. But what would be the effect of using $W1 or W$1. That is, making either the column or the row absolute and the other relative?

        Alan

    • #795453

      Method 1:
      – Select all cells in column Q that you want to apply conditional formatting to, say Q1:Q100.
      – Select Format | Conditional Formatting…
      – Set up the conditional formatting for the active cell in the selected range (probably the first cell)
      – This is essential: make sure that the reference to the cell in column W is relative; the default is to make it absolute by including $ characters in the cell reference; remove these $ characters: =W1 instead of =$W$1.
      – Click OK. Since the cell reference in conditional formatting is relative, it will adapt itself for the other cells.

      Method 2:
      – Select the first cell in column Q that you want to format, say cell Q1.
      – Select Format | Conditional Formatting…
      – Set up conditional formatting, and as above, make sure that the reference to the cell in column W is relative: =W1 instead of =$W$1.
      – Click OK.
      – Click the Format Painter button on the toolbar.
      – Select the other cells in column Q that you want to format.

    • #795456

      Thanks, Hans, both those methods are what I’ve been trying all afternoon and neither works for me.
      I want the text in W to be red if the value in W is less than the value in Q.
      I highlight cell Q2, go to conditional formatting and set up the condition.
      Condition 1
      Cell Value Is less than =”q2″

      I just type in Q2, the = sign and quotation marks are put in automatically.

      Whatever way I try to apply this to the other cells in column W, they only compare themselves to Q2.

      Am I making myself clear?
      Thanks
      Jim

      • #795467

        Literally add

        =Q2

        By not adding the equal yourself, excel is assuming you want to compare to a string (=”q2″) not the contents of cell (=Q2)

        Steve

        • #795479

          Wonderful! That did it.

          Thanks Steve and Hans both.

          • #795492

            I’m glad you got it to work. Just for the record: if, instead of typing Q2 in the box, you point to cell Q2, Excel will create the formula =$Q$2 (at least, it does so in my Excel 2002′); you can then remove the $’s or press F4 three times until they are gone.

            • #795501

              Thanks, Hans.
              Out of interest, do you know where to get the patch to cure the problem of Autosave needing to be re-set every time I open Excel?

              Jim

            • #795508

              According to MSKB article XL2000: AutoSave Settings Are Not Retained Between Sessions of Excel 2000, this problem has been solved in Excel 2000 SR-1/SR-1a.

              WMVP Pieterse has an alternative for AutoSave: his free AutoSafe add-in is more convenient, since it makes a backup copy in another folder instead of overwriting the original. You can download it from his website http://www.jkp-ads.com[/url%5D.

              For the future: if you have a question that is completely unrelated to the original one, it is better to start a new thread; that makes it easier to find for others browsing this forum. smile

            • #795509

              According to MSKB article XL2000: AutoSave Settings Are Not Retained Between Sessions of Excel 2000, this problem has been solved in Excel 2000 SR-1/SR-1a.

              WMVP Pieterse has an alternative for AutoSave: his free AutoSafe add-in is more convenient, since it makes a backup copy in another folder instead of overwriting the original. You can download it from his website http://www.jkp-ads.com[/url%5D.

              For the future: if you have a question that is completely unrelated to the original one, it is better to start a new thread; that makes it easier to find for others browsing this forum. smile

            • #795502

              Thanks, Hans.
              Out of interest, do you know where to get the patch to cure the problem of Autosave needing to be re-set every time I open Excel?

              Jim

          • #795493

            I’m glad you got it to work. Just for the record: if, instead of typing Q2 in the box, you point to cell Q2, Excel will create the formula =$Q$2 (at least, it does so in my Excel 2002′); you can then remove the $’s or press F4 three times until they are gone.

        • #795480

          Wonderful! That did it.

          Thanks Steve and Hans both.

      • #795468

        Literally add

        =Q2

        By not adding the equal yourself, excel is assuming you want to compare to a string (=”q2″) not the contents of cell (=Q2)

        Steve

    • #795457

      Thanks, Hans, both those methods are what I’ve been trying all afternoon and neither works for me.
      I want the text in W to be red if the value in W is less than the value in Q.
      I highlight cell Q2, go to conditional formatting and set up the condition.
      Condition 1
      Cell Value Is less than =”q2″

      I just type in Q2, the = sign and quotation marks are put in automatically.

      Whatever way I try to apply this to the other cells in column W, they only compare themselves to Q2.

      Am I making myself clear?
      Thanks
      Jim

    Viewing 3 reply threads
    Reply To: Conditional Formatting (Excel 2K)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: