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
Remarks
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.
Example
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)"
Range("B2:B26").Select
' Applying Conditional Formatting Top 10
Selection.FormatConditions.AddTop10
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
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