+ Reply to Thread
Results 1 to 3 of 3

Conditional Format - Highlight based on the bottom row

  1. #1
    Registered User
    Join Date
    03-17-2008
    Posts
    16

    Conditional Format - Highlight based on the bottom row

    I'm having issues coming up with a conditional format rule that highlights the top cell of a column based on if the bottom cell of that column has the greatest value in it's row.

    This needs to be applied to 13 columns, so I want to highlight the top cell of the column that has the greatest value (based on the bottom row) of the 13 columns. I know I can do this via:

    =AND($J$45>$L$45,$J$45>$M$45, $J$45>$N$45......etc etc applied to the top of the column. However, I would prefer not to do this for 13 columns 13 times. In the interest of expanding my knowledge, I wanted to find a simpler way. I thought of using the MAX function, but the bottom row of the column has formulas that result in a #DIV/0 error. Any way to do this?

    Thanks!
    Last edited by MadCrammer; 08-24-2010 at 01:14 PM.

  2. #2
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Conditional Format - Highlight based on the bottom row

    Highlight all top rows of all 13 columns and try to use this formula (Eg assuming that your table within range L22:X45, highlight range L22:X22)
    =$J$45>L$45
    Last edited by contaminated; 08-24-2010 at 12:40 PM.
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, ?Born in USSR?
    Vusal M Dadashev

    Baku, Azerbaijan

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Conditional Format - Highlight based on the bottom row

    Assuming the bottom row is J45:V45 then select the top row (starting at column J) and use this formula in conditional formatting

    =J45=MAX(IF(ISNUMBER($J45:$V45),$J45:$V45))
    Audere est facere

+ 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