+ Reply to Thread
Results 1 to 9 of 9

Conditional Formatting of Max Value based on another cell

  1. #1
    Registered User
    Join Date
    05-23-2013
    Location
    usa
    MS-Off Ver
    Excel 2010
    Posts
    4

    Conditional Formatting of Max Value based on another cell

    I'm trying to conditionally highlight the greatest value of a column for each set of items in a large table.

    For example, I have a table with the following info:
    Fruit, Location, Amount
    Apples, Fridge, 3
    Apples, Counter, 2
    Bananas, Fridge, 1
    Bananas, Counter 4

    I'd like to highlight the number 3 in column "Amount" for the first entry and the number 4 for "Amount" in the fourth entry since those are the two greatest counts of each given "Fruit."

    Any help with this is greatly appreciated!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Conditional Formatting of Max Value based on another cell

    Hi and welcome to the forum

    See if this will work for you...

    1. highlight the range you want to apply the conditional formatting to
    2. on the home tab, styles, select CF
    3. select new rule, select use formula
    4. enter =IF(COUNTIF($A$2:$A2,$A2)=1,MAX(IF($A$2:$A$7=$A2,$C$2:$C$7,0)),"") format fill as needed
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    05-23-2013
    Location
    usa
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Conditional Formatting of Max Value based on another cell

    Appreciate the response!

    I tried using the formula, but it just ended up highlighting the header in yellow :/

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Conditional Formatting of Max Value based on another cell

    I changed the formula a bit, see the attached
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-23-2013
    Location
    usa
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Conditional Formatting of Max Value based on another cell

    Seems to be getting closer, but still isn't behaving in a way that I can figure out.

    I've had to anonymize my data, but attached is a snippet of the result when i applied it to a larger table.

    example.xlsx

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Conditional Formatting of Max Value based on another cell

    OK 1st, you have only applied the CF to B:B (on a side note, CF takes up quite a bite of resourses, so its always best to restrict it to the smallest reange practical)

    2nd, Im not sure how this happenedm but you seem to have "adjusted" a few cell ref's in my formula...
    =IF(MAX(IF($A$2:$A$999=$A3,$B$2:$B$999,0))=$B3,MAX(IF($A$2:$A$999=$A3,$B$2:$B$999,0)),"")
    =IF(MAX(IF($A$2:$A$999=$A2,$B$2:$B$999,0))=$B2,MAX(IF($A$2:$A$999=$A2,$B$2:$B$999,0)),"")

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Conditional Formatting of Max Value based on another cell

    Try this...

    Assume the Amounts are in the range C2:C5.

    Select the *entire* range C2:C5 starting from cell C2.
    Cell C2 will be the active cell. The active cell is the
    one cell in the selected range that is not shaded. The
    formula will be relative to the active cell.

    Goto the Home tab>Styles>Conditional Formatting>
    Manage rules>New rule>Use a formula to determine
    which cells to format

    Enter this formula in the box below:

    =C2=MAX(IF(A$2:A$5=A2,C$2:C$5))

    Click the Format button
    Select the desired style(s)
    OK out
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  8. #8
    Registered User
    Join Date
    05-23-2013
    Location
    usa
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Conditional Formatting of Max Value based on another cell

    Quote Originally Posted by Tony Valko View Post
    Try this...

    Assume the Amounts are in the range C2:C5.

    Select the *entire* range C2:C5 starting from cell C2.
    Cell C2 will be the active cell. The active cell is the
    one cell in the selected range that is not shaded. The
    formula will be relative to the active cell.

    Goto the Home tab>Styles>Conditional Formatting>
    Manage rules>New rule>Use a formula to determine
    which cells to format

    Enter this formula in the box below:

    =C2=MAX(IF(A$2:A$5=A2,C$2:C$5))

    Click the Format button
    Select the desired style(s)
    OK out
    This worked! Thanks so much

    @FDibbins - I had modified the values when adapting it to my much larger spreadsheet. I may have caused an error that caused it to go haywire. I really appreciate the help

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Conditional Formatting of Max Value based on another cell

    You're welcome. Thanks for the feedback!

+ 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