Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Taunton, Somerset, England
    Posts
    27
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Conditional Formatting (Excel 2K)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting (Excel 2K)

    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.

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Taunton, Somerset, England
    Posts
    27
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Excel 2K)

    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

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    Taunton, Somerset, England
    Posts
    27
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Excel 2K)

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

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Formatting (Excel 2K)

    Literally add
    <pre>=Q2</pre>


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

    Steve

  6. #6
    Lounger
    Join Date
    Jan 2001
    Location
    Taunton, Somerset, England
    Posts
    27
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Excel 2K)

    Wonderful! That did it.

    Thanks Steve and Hans both.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting (Excel 2K)

    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.

  8. #8
    Lounger
    Join Date
    Jan 2001
    Location
    Taunton, Somerset, England
    Posts
    27
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Excel 2K)

    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

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting (Excel 2K)

    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 <!profile=Pieterse>Pieterse<!/profile> 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.

    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. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Conditional Formatting (Excel 2K)

    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

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Excel 2K)

    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

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Formatting (Excel 2K)

    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

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting (Excel 2K)

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

  14. #14
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Conditional Formatting (Excel 2K)

    <P ID="edit" class=small>(Edited by fburg on 07-Mar-04 16:32. lined up col Q and W letters)</P>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

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting (Excel 2K)

    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.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •