Table of Contents
The VBA code can be used to highlight the top n values in a column by sorting the column in descending order and applying a conditional formatting rule using the nth largest value as the criteria. This will highlight all cells in the column with a value greater than or equal to the nth largest value. This can be done by writing a macro that calls the Sort and ConditionalFormatting methods of the Range object.
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.