+ Reply to Thread
Results 1 to 10 of 10

How to color maximum number by Green and minimum number by Red..

  1. #1
    Registered User
    Join Date
    04-27-2015
    Location
    Perth
    MS-Off Ver
    office 2010
    Posts
    76

    How to color maximum number by Green and minimum number by Red..

    I need to color a cell green with maximum number and by red with minimum number. These numbers are grouped by data in column A.

    I have attached excel and also image here. I need the output as shown in column D. Waiting for help

    Race.png
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,041

    Re: How to color maximum number by Green and minimum number by Red..

    Select D2

    Conditional Formatting
    New Rule
    Use a formula to determine...
    Two formulas required

    =(D2=MAX(D$2:D$1000))
    format as green

    =(D2=MIN(D$2:D$1000))
    format as red

    Adjust D$2:D$1000 to suit your range

    Use Format painter (paintbrush icon) to copy to other cells
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    04-27-2015
    Location
    Perth
    MS-Off Ver
    office 2010
    Posts
    76

    Re: How to color maximum number by Green and minimum number by Red..

    Hi K, I did all the steps as written by you but not giving desired output. Can you attach an excel..

  4. #4
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: How to color maximum number by Green and minimum number by Red..

    I have taken a different approach, so that min and max for each race is available
    Just run the macro

    The macro:
    Sorts data by race
    Puts in subtotals for MIN, then compares rows above until it finds a match for each MIN and colours cell
    Removes subtotals
    Repeats the process for MAX
    Removes subtotals

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Kevin#; 03-02-2016 at 11:20 AM.

  5. #5
    Registered User
    Join Date
    04-27-2015
    Location
    Perth
    MS-Off Ver
    office 2010
    Posts
    76

    Re: How to color maximum number by Green and minimum number by Red..

    Thank you kevin for your effort in writing this macro. Macro is working and giving the desired output. Amazing....

  6. #6
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: How to color maximum number by Green and minimum number by Red..

    glad it works for you and thanks for feedback.

    Please mark the thread as solved.

  7. #7
    Registered User
    Join Date
    10-19-2013
    Location
    England
    MS-Off Ver
    Excel 2007/2010
    Posts
    10

    Re: How to color maximum number by Green and minimum number by Red..

    The conditional formatting will just highlight the highest and lowest in the column.
    If I'm interpreting correctly; you're after the highest and smallest value in each set, not the column as a whole.

    My quick'n'dirty solution is to use an array formula to find the value you're looking for. Use this to populate another column, or in the conditional formatting:

    Max is easy:
    an array to give the value in a column: {=MAX((A:A=A1)*C:C)}
    and as Conditional Formatting: =C1=MAX(($A:$A=$A1)*$C:$C)

    Min requires a number larger than the actual values could ever be (the 99999999 figure);
    Array to give the value in a column: {=MIN(IF($A$1:$A$1000=$A1,1,99999999)*$C$1:$C$1000)}
    To use in Conditional Formatting: =C1=MIN(IF($A$1:$A$1000=$A1,1,99999999)*$C$1:$C$1000)
    note that the range needs to be adjusted to suit.

    I know the vba solution has been used; just seemed interesting when the minimum was required...
    Last edited by Googlyhead; 03-02-2016 at 12:24 PM.

  8. #8
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be anywhere in the world
    MS-Off Ver
    power BI
    Posts
    9,052

    Re: How to color maximum number by Green and minimum number by Red..

    With no macro:

    1. select entire data in D col
    2. in CF, new rule for green:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    3. in CF new rule for red:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    4. OK
    Last edited by sandy666; 03-02-2016 at 12:46 PM.

  9. #9
    Registered User
    Join Date
    04-27-2015
    Location
    Perth
    MS-Off Ver
    office 2010
    Posts
    76

    Re: How to color maximum number by Green and minimum number by Red..

    Hi googly and sandy. The formula that you both provided giving the desired output. Actually, I receive these excels everyday(around 4 or 5 daily) and number of rows varies in each and every excel i.e yesterday I worked in a excel containing 345 rows, today 150 and so on. So I have to adjust the range in conditional formatting in each and every excel. Vba solution works just by clicking a single button

  10. #10
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be anywhere in the world
    MS-Off Ver
    power BI
    Posts
    9,052

    Re: How to color maximum number by Green and minimum number by Red..

    We are not saying it is better or worse solution. We are saying it is another point of view on the same problem.

    What will you do with VBA (if you don't know VBA) if you will change column in the future from D to, say AA?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Maximum/Minimum value from range and then from cell color
    By mughal1990 in forum Excel General
    Replies: 4
    Last Post: 04-19-2015, 02:45 AM
  2. [SOLVED] Find Minimum Number From same number range.
    By sanju2323 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-24-2015, 03:02 AM
  3. Return multiple of a number based on a maximum and minimum quantity
    By DanOlson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-15-2015, 12:24 PM
  4. Replies: 2
    Last Post: 10-03-2014, 03:46 AM
  5. [SOLVED] Determine if whole number exists between a minimum and maximum value
    By ocgiraffe in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-14-2014, 11:07 AM
  6. Color maximum and Minimum
    By thong127 in forum Excel General
    Replies: 6
    Last Post: 01-09-2014, 11:20 AM
  7. Replies: 2
    Last Post: 10-17-2013, 02:57 PM

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