Macro to Highlight the Largest and Smallest Numbers

This VBA macro finds the largest and smallest numbers in the selected cells and highlights them, green for the largest and red for the smallest. A second macro highlights the top 3.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Highlight the largest and smallest

Sub HighlightMaxMin()
    Dim c As Range, mx As Double, mn As Double
    mx = Application.Max(Selection)
    mn = Application.Min(Selection)
    For Each c In Selection
        If Not IsEmpty(c) And IsNumeric(c.Value) Then
            If c.Value = mx Then c.Interior.Color = RGB(198, 239, 206)
            If c.Value = mn Then c.Interior.Color = RGB(255, 199, 206)
        End If
    Next c
End Sub

If the largest value appears twice, both cells are highlighted.

Highlight the top 3

Sub HighlightTop3()
    Dim c As Range, third As Double
    third = Application.Large(Selection, 3)
    For Each c In Selection
        If Not IsEmpty(c) And IsNumeric(c.Value) Then
            If c.Value >= third Then c.Interior.Color = RGB(198, 239, 206)
        End If
    Next c
End Sub

Change 3 to highlight the top 5 or top 10. The selection needs at least that many numbers.

Without a macro: conditional formatting

  1. Select the numbers.
  2. Go to Home → Conditional Formatting → Top/Bottom Rules → Top 10 Items.
  3. Change 10 to 1 (or 3) and pick a format.

Conditional formatting updates automatically when the numbers change; the macro colours the cells once. To find the values with formulas, see LARGE, SMALL and Top N with a formula.

Things to know

  • The macro skips text and blank cells.
  • Cells containing errors such as #N/A make MAX fail. Fix or exclude them first.
  • To clear the colours, select the cells and choose Home → Fill Color → No Fill.

Related macros