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
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
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.
Hi K, I did all the steps as written by you but not giving desired output. Can you attach an excel..
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.
Last edited by Kevin#; 03-02-2016 at 11:20 AM.
Thank you kevin for your effort in writing this macro. Macro is working and giving the desired output. Amazing....
glad it works for you and thanks for feedback.
Please mark the thread as solved.
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.
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
Last edited by sandy666; 03-02-2016 at 12:46 PM.
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
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)
Bookmarks