Skip to content

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.