Macro to Find the Largest Number in a Selection in Excel

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

Last updated · Written by Ajay Anand, Microsoft Excel MVP