+ Reply to Thread
Results 1 to 4 of 4

Conditional formatting: Non-adjacent cells, format highest value and cell next to it

  1. #1
    Registered User
    Join Date
    07-18-2012
    Location
    Seattle, Washington
    MS-Off Ver
    Current
    Posts
    6

    Conditional formatting: Non-adjacent cells, format highest value and cell next to it

    I am using Excel 2007. My data is illustrated in the table below.

    I am trying to identify the highest percentage in each row, then format that cell, plus the cell next to it (on the left).
    For example, in Row 1, since 24% is the highest value, I want to format E1 and D1. In Row 2, I want to format C2 and B2. In Row 3, I want to format G3 and F3.

    I am not sure what formula to use for the conditional formatting. Thanks for any help anyone can give!

    A B C D E F G
    1 Text1 4 11% 9 24% 2 5%
    2 Text2 13 34% 6 16% 2 5%
    3 Text3 1 3% 11 29% 12 32%

  2. #2
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Conditional formatting: Non-adjacent cells, format highest value and cell next to it

    Try

    Select B1,D1,F1 and use: =INDEX(B1:C1,2)=MAX($C1,$E1,$G1)
    Select C1,E1,G1 and use: =C1=MAX($C1,$E1,$G1)

    Now select B1:G1 and double click the format painter and click on B2 and B3, then cancel the format painter
    Last edited by Cutter; 07-19-2012 at 09:07 AM. Reason: Changed 1st formula from OFFSET()

  3. #3
    Registered User
    Join Date
    07-18-2012
    Location
    Seattle, Washington
    MS-Off Ver
    Current
    Posts
    6

    Re: Conditional formatting: Non-adjacent cells, format highest value and cell next to it

    Maybe I did something wrong - this didn't work.

  4. #4
    Registered User
    Join Date
    07-18-2012
    Location
    Seattle, Washington
    MS-Off Ver
    Current
    Posts
    6

    Re: Conditional formatting: Non-adjacent cells, format highest value and cell next to it

    I ended up using a workaround -- I used the "Format only top or bottom ranked values" rule for B1, D1, F1, then created a second rule that did the same for C1, E1, G1.

    Since the largest # will always correspond with the largest percentage, this worked for my data.
    I would be interested in a better formula for more complicated data, however.

    Thanks for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1