VBA: How to Highlight Top N Values in Column

You can use the following syntax in VBA to highlight the top N values in a column in Excel:

Sub HighlightTopN()

    Dim rng As Range
    Dim EntireRange As Range
    
    'specify range to use
    Set EntireRange = Range("A2:A11")
    
    'highlight top 3 values in range
    For Each rng In EntireRange
        
        For i = 1 To 3
        
        If rng.Value = WorksheetFunction.Large(EntireRange, i) Then
        rng.Interior.Color = vbYellow
        End If
        Next
    
    Next rng

End Sub

This particular macro will highlight the top 3 largest values in the range A2:A11.

To highlight a different number of top values, simply change the line For i = 1 To 3 to have a different upper bound.

The following example shows how to use this syntax in practice.

Example: Highlight Top N Values in Column Using VBA

Suppose we have the following values in column A of our Excel worksheet:

We can create the following macro to highlight the top 3 largest values in the range A2:A11:

Sub HighlightTopN()

    Dim rng As Range
    Dim EntireRange As Range
    
    'specify range to use
    Set EntireRange = Range("A2:A11")
    
    'highlight top 3 values in range
    For Each rng In EntireRange
        
        For i = 1 To 3
        
        If rng.Value = WorksheetFunction.Large(EntireRange, i) Then
        rng.Interior.Color = vbYellow
        End If
        Next
    
    Next rng

End Sub

When we run this macro, we receive the following output:

Notice that the cells with the top 3 largest values in column A are now highlighted.

Note that you can also change the color to use for highlighting along with the number of top values to highlight.

For example, we can use the following macro to highlight the top 5 values in column A in green:

Sub HighlightTopN()

    Dim rng As Range
    Dim EntireRange As Range
    
    'specify range to use
    Set EntireRange = Range("A2:A11")
    
    'highlight top 5 values in range
    For Each rng In EntireRange
        
        For i = 1 To 5
        
        If rng.Value = WorksheetFunction.Large(EntireRange, i) Then
        rng.Interior.Color = vbGreen
        End If
        Next
    
    Next rng

End Sub

When we run this macro, we receive the following output:

Notice that the cells with the top 5 largest values in column A are now highlighted in green.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Apply Conditional Formatting to Cells
VBA: How to Apply Conditional Formatting to Duplicate Values
VBA: How to Find Value in Column