This documentation is archived and is not being maintained.

Top10 Object

Office 2007
Represents a top ten visual of a conditional formatting rule. Applying a color to a range helps you see the value of a cell relative to other cells.

Version Information
 Version Added:  Excel 2007


All conditional formatting objects are contained within a FormatConditions collection object, which is a child of a Range collection. You can create a top 10 formatting rule by using either the Add or AddTop10 method of the FormatConditions collection.

The following example builds a dynamic data set and applies color to the top 10 values through conditional formatting rules.

Visual Basic for Applications
Sub Top10CF()

' Building data
    Range("A1").Value = "Name"
    Range("B1").Value = "Number"
    Range("A2").Value = "Agent1"
    Range("A2").AutoFill Destination:=Range("A2:A26"), Type:=xlFillDefault
    Range("B2:B26").FormulaArray = "=INT(RAND()*101)"
' Applying Conditional Formatting Top 10
    With Selection.FormatConditions(1)
        .TopBottom = xlTop10Top
        .Rank = 10
        .Percent = False
    End With
' Applying color fill
    With Selection.FormatConditions(1).Font
        .Color = -16752384
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13561798
        .TintAndShade = 0
    End With
MsgBox "Added Top10 Conditional Format.  Press F9 to update values.", vbInformation

End Sub