Excel VBA – Conditionally Format Top/Bottom 10 Using VBA Macro

To conditionally format x top/bottom values in a range use the AddTop10 method of the FormatConditions object. You can then specify:

using the TopBottom property whether you want to format xlTop10Top or xlTop10Bottom;

using the Rank property, x number of values to format

using the Percent property whether x refers to x percent or not.

Sub topandbottomvalues()
Dim rg As Range
Dim tt As Top10
Set rg = Range("O4", Range("O4").End(xlDown))
Set tt = rg.FormatConditions.AddTop10
With tt
.TopBottom = xlTop10Top
.Rank = 1
.Percent = False
.Interior.Color = vbRed
End With
End Sub

The code above relates to the data show below.