highlight highest 1st,2nd and 3rd values from two columns

[vinu9092]

TOP FUNCTION highlight top three values from selected range but with same color for top three.i want different color for all three values means golden color for 1st highest, silver color for 2nd highest and yellow color for 3rd .
i have values in c31 to c41 in percentage in first column and E31 to E41 in percentage in second column.want to select highest values from both columns comparing simultaneously?

 

[wfsteadman]

You can use the Large Function to find the nth largest number in a range of cells. You can setup conditional formatting that looks like this for the =$C31=LARGE($C$31:$C$41,3) and set that color, this will highlight the 3rd highest value. You can then do it for the second largest by changing the last 3 in the formula to a 2. You can see a decent video here on the subject: https://www.youtube.com/watch?v=eyAN5ei4m04

 

[vinu9092]

You can use the Large Function to find the nth largest number in a range of cells. You can setup conditional formatting that looks like this for the =$C31=LARGE($C$31:$C$41,3) and set that color, this will highlight the 3rd highest value. You can then do it for the second largest by changing the last 3 in the formula to a 2. You can see a decent video here on the subject: https://www.youtube.com/watch?v=eyAN5ei4m04

Want highest value from both columns ,not for individual column.above formula is for individual column.TOP function gives correct result when both ranges are selected but conditional formatting can't be changed for individual cells.

 

[wfsteadman]

You can do the conditional formatting on each of the different columns separately. I would make a conditional format for Column C Range and Conditional Format for Column E. If it were me, I would probably just make 6 Cells in a 3 row 2 column grid and to the Top on the Ranges, the Large 2 and Large 3 on the ranges then the values you seek are side by side. But that is just how I would do it.

 

[vinu9092]

TOP function gives proper result only problem is on different color for 1st,2nd and 3rd highest.how to post screenshot on this site?

 

[Gravitas]

Click on cell C31, click on conditional formatting -> New Rule -> Use a formula which cell to format, and enter the following:
=$C31>$E31
Format the background colour to what you would like. Then copy cell C31 and highlight all of column C. Do a paste special and only copy the format.
Do the same with column E but use following formula:
=$E31>$C31
Hope this helped.

 

[vinu9092]

Click on cell C31, click on conditional formatting -> New Rule -> Use a formula which cell to format, and enter the following:
=$C31>$E31
Format the background colour to what you would like. Then copy cell C31 and highlight all of column C. Do a paste special and only copy the format.
Do the same with column E but use following formula:
=$E31>$C31
Hope this helped.

not working.highest value must be highest of two column like top 3 values of entire range not individual column.
i found working with select range, conditionl formating .cell value equal to then =LARGE($C:$E,1) THEN IN color golden for 1st and rest changing for color.but i only want for my range i.e C31:C41 AND E31:C41 NOT FOR ENTIRE COLUMN.

 

[gior]

Possible to use user defined function? Function NewTop merges two ranges r1 and r2, returns i-th highest value (similar to Large Function)

Function NewTop(r1 As Range, r2 As Range, i As Integer) As Integer
  NewTop = Application.WorksheetFunction.Large(Union(r1, r2), i)
End Function

In conditional formatting to use formulas
=c31=NewTop($C$31:$C$41, $E$31:$E$41, 1) for First (maximum) value
=c31=NewTop($C$31:$C$41, $E$31:$E$41, 2) for Second value
=c31=NewTop($C$31:$C$41, $E$31:$E$41, 3) for Third value

 

[RoryA]

Duplicate of this thread: http://www.mrexcel.com/forum/excel-questions/983283-highlight-cell-having-top-3-values.html

Closing this one.

PLEASE DO NOT POST THE SAME QUESTION MULTIPLE TIMES.