Highlight highest value

This tutorial shows how to highlight the highest number in a range using Excel and VBA

EXCEL METHOD 1. Highlight highest value

EXCEL

Select range > Home tab > Style group > Conditional Formatting > New Rules > Format only top or bottom ranked values > Select Top > Enter 1 > Format > Fill tab > Select color > OK > OK
1. Select the range in which you want to highlight the highest number.
Note: In this example we are selecting a range of cells from B3 to B9. 		Select range of cells - Highlight highest value
2. Select the Home tab. Select Home tab - Excel 2016
3. Click on Conditional Formatting in the Style group.
4. Click on New Rules. 		Select Conditional Formatting in Style group and click New Rules
5. Select Format only top or bottom ranked values.
6. Select the Top option from the drop down menu.
7. Enter a value of 1 in the input box menu.
Note: By default this value in 10.
8. Click on the Format button. 		Select options to highlight highest number
9. Click on the Fill tab and select the color that you want to highlight the cell that contains the highest value.
10. Click on OK. 		Select the Fill tab, select color and click OK
11. Click OK in the New Formatting Rule dialog box. Click OK in New Formatting Rule
12. This image show the result of these steps
Note: In this example the largest number in the selected range (B3:B9) is 700, which is captured in cell B7 and now highlighted in the color that we selected. 		Highlighted highest value

VBA METHOD 1. Highlight highest value using VBA

VBA

Sub Highlight_highest_value()
'declare variables

Dim ws As Worksheet
Dim ColorRng As Range
Dim ColorCell As Range

Set ws = Worksheets("Sheet1")
Set ColorRng = ws.Range("B3:B9")
'highlight the cell that contains the highest number
For Each ColorCell In ColorRng

If ColorCell.Value = Application.WorksheetFunction.Max(ColorRng) Then
ColorCell.Interior.Color = RGB(220, 230, 248)
Else
ColorCell.Interior.ColorIndex = xlNone
End If

Next

End Sub

NOTES
Note 1: This VBA code will highlight the cell that contains the largest number in range B3 to B9 of "Sheet1".

RELATED TOPICS
Related Topic Description Related Topic and Description
Highlight cells with duplicate values How to highlight cells that contain duplicate values in a selected range
Highlight cells with unique values How to highlight cells that contain unique values in a selected range