Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Highlighting Cell based on highlighted cells to the right

    I have Branch names in column A and Values in columns B to K from row 6 to 25

    I would like to set up conditional formatting if possible so that where there are any values that are highlighted to the right of ColumnA for eg if D6 is highlighted, then A6 must also be highlighted

    Your assistance will be most appreciated
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Howard,

    A very interesting situation. You are setting the color of these cells via conditional formatting. When I try to test the interior color of any of the cells they all return the value of a normal cell 16777215. However, if I manually set a cell to a color it will return the proper color code, i.e. Red = 192. Go Figure.


    P.S. The attached .xlsm file you can run the Test macro to see the results. I tried to create a User Defined Function {IsHighlight} to use in the conditional formatting, but of course that didn't work.

    Attachment 28965Attachment 28966
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by RetiredGeek; 2011-08-24 at 14:00.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi RG

    Thanks for the reply. The color of the cells in Cols B:K is determined by conditional formatting.

    Is is possible to set up a macro or conditional formmatting so that if any cell to the right of column A is highlighted due to conditional formatting, The cell in Col A will also be highlighted?

  4. #4
    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
    Set up the formula rule:
    =MAX(B6:K6/$B$33:$K$33)>1.05

    Steve

  5. #5
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts
    Just a thought, insted of testing for a colour, test for the lack of colour. Then any cell that fails the test could then cause the cell in column A to become coloured?
    Paul Coyle
    Approach love and cooking with reckless abandon

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Paul,

    That is what I was trying to do. The long number {16777215} is what is returned by any cell which has not been colored and also those colored via conditional formatting.

    Steve,

    Nicely done!

    Could you please explain your logic for the rest of us?

    Thanks
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    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
    Could you please explain your logic for the rest of us?
    Each of the individual cond formats is checking if the cell is > 5% over the value in row 33 (1.05*rpw 33). Thus I checked each value (B6/B33, c6/C33, etc) and checked the maximum. If the max is over 1.05 than at least one column in that row met the individual cond format and should be colored.. [The formula is an Array formula which cond implicitly works with...]

    Steve

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Steve,

    Thanks.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve & RG

    Thanks for the help.

    The formula supplied by Steve =MAX(B6:K6/$B$33:$K$33)>1.05 works perfectly

Posting Permissions

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