Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional formatting (2003)

    Very simplified question but will provide the answer I need. (I hope)

    I have a SS in which the cells of several columns (B through D) are conditionally formatted to have a certain color (same color for each of them) based on complex formulas.

    I would like to conditionally format the cells in column A such that if any of the cells on a particular row display the conditionally formatted color, then the corresponding cell in column A will also display that color.

    I can construct a formula that covers all the conditions but it is very long and cumbersome. I was hoping there might be a way to create an OR formula for the cell colors of the conditionally formatted cells. For example the conditional format formula for cell A5 would be =OR(B5=COLOR,C5=COLOR,D5=COLOR)

    Problem is, I don't know how to find the color of the cell. I am using one of the standard colors in the pattern palette.

    Any ideas?

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Conditional formatting (2003)

    You can't do that - even if you use VBA to get the cells interior.color, it will not reflect the conditional formatting colour. You are stuck with either using the complex formula or, if only one of them will match at a time, just apply the conditional formats to the entire range.
    You can write code to loop through the format conditions for each cell, constructing the formulas and then comparing them to see if they apply, but it's flaky!
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    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)

    Chip Pearson has some of the "Flaky" functions (that were alluded to) to determine the Conditional Formatting Colors

    Steve

  4. #4
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formatting (2003)

    I was afraid that might be the case. Thanks for the input.

  5. #5
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formatting (2003)

    Chip has sure got some cool code but i think I will take another direction on this... more coding effort than this project is worth. Thanks for the pointer to Chip's site.

Posting Permissions

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