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

Status
Not open for further replies.

#### vinu9092

##### New Member
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?

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

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

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

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

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

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

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

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

Code:
``````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

Status
Not open for further replies.

Replies
2
Views
133
Replies
1
Views
376
Replies
2
Views
361
Replies
1
Views
259
Replies
1
Views
368

1,191,693
Messages
5,988,139
Members
440,129
Latest member
bianca88

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back