Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Conditional Formatting (2003 SP1)

    Is there a way to use the auto fill or format painter to conditionally format a cell based from an adjacent cells data? I can conditionally format one cell with another cells data by using the conditional format option with a formula pointing to an adjacent cell. I am looking for a shortcut to copy this through to multiple cells in a column based on information from their respective adjacent cells.

    Thank you

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

    Re: Conditional Formatting (2003 SP1)

    If you want the conditional formatting to refer to the cell next to the cell with the formatting, you must make sure that the formula uses relative addressing, for example in B1:

    =(A1>0)

    If you copy the formatting to D1, the conditional formatting formula will automatically be adjusted to

    =(C1>0)

    If the formula uses absolute formatting, for example

    =($A$1>0)

    it will remain the same wherever the formatting is copied.

  3. #3
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (2003 SP1)

    Thank you Hans. Is there a way to copy this down now so that B2 will conditionally format by the value in A2>0 ... and so on down through multiple rows of cells in column B?

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

    Re: Conditional Formatting (2003 SP1)

    Yes: if you used the formula =(A1>0) in conditional formatting for B1, you can copy this down in one of the following ways:

    1) Use the format painter, or

    2) Copy B1 to the clipboard, then select the target cells in column B.
    Select Edit | Paste Special, click the Formats option, and finally click OK.

  5. #5
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (2003 SP1)

    Thank you Hans.
    When I try either of these procedures, all of the cells in column B are referencing A1and likewise conditionally format according to the value in A1. I would like to get B2 to conditionally format referencing A2, B3 to A3, B4 to A4...
    I often times auto fill cells in a column based on the values in the cells above by selecting these cells above, grabbing the lower right hand corner of the selected cells when the cursor changes to a bold black cross-hair, dragging down until I have all of the cells selected that I want filled in, and then releasing the mouse button. I am trying to achieve the same idea, only this time, with conditional formatting.

  6. #6
    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 (2003 SP1)

    In the formula used for cond formatting: if the reference is absolute in columns and rows (eg $a$1) it will be copied and refer to just this cell. If you use a complete relative formula (eg A1) it will be relative to this when the format is copied.

    It can also be constant to column ($A1) or to row (A$1) to lock to location to a column or row.

    The copying of the cond format formulas is just like the copying of regular formula.

    Steve

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

    Re: Conditional Formatting (2003 SP1)

    Are you absolutely sure that the formula you used in the conditional formatting dialog doesn't use absolute addressing (i.e. $A$1 etc.)?

    If you can't get it to work, could you post a small sample workbook demonstrating the problem? Remove sensitive information or replace it with dummy data.

  8. #8
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (2003 SP1)

    Oh yes I understand now. Thank you for your help Hans and Steve and I apologize for the confusion.

Posting Permissions

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