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

1. ## 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

2. ## 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

Use Format painter (paintbrush icon) to copy to other cells

3. ## 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. ## 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.``

5. ## 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. ## Re: How to color maximum number by Green and minimum number by Red..

glad it works for you and thanks for feedback.

7. ## 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...

8. ## 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:
`Please Login or Register  to view this content.`

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

4. OK

9. ## 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. ## 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?

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

#### 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