IconSetCondition Object - Microsoft Office Excel

Version Information
Version Added: Excel

Remarks

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

Each icon set contains three, four, or five icons. You use the IconSets property of the Workbook object to return an IconSets object to specify one of the built-in icon sets. Each individual icon in the icon set is then assigned to a subset of the values of the range by the members of the IconCriteria obect. The type of threshold is also specified by this object.

Example

The following code example creates a range of numbers representing test scores and then applies an icon set conditional formatting rule to that range. The type of icon set is then changed from the default icons to a five-arrow icon set. Finally, the threshold type is modified from percentile to a hard-coded number.

Visual Basic for Applications

Visual Basic for Applications
Sub CreateIconSetCF() Dim cfIconSet As IconSetCondition 'Fill cells with sample data from 1 to 10 With ActiveSheet .Range("C1") = 55 .Range("C2") = 92 .Range("C3") = 88 .Range("C4") = 77 .Range("C5") = 66 .Range("C6") = 93 .Range("C7") = 76 .Range("C8") = 80 .Range("C9") = 79 .Range("C10") = 83 .Range("C11") = 66 .Range("C12") = 74 End With Range("C1:C12").Select 'Create an icon set conditional format for the created sample data range Set cfIconSet = Selection.FormatConditions.AddIconSetCondition 'Change the icon set to a five-arrow icon set cfIconSet.IconSet = ActiveWorkbook.IconSets(xl5Arrows) 'The IconCriterion collection contains all IconCriteria 'By indexing into the collection you can modify each criterion With cfIconSet.IconCriteria(1) .Type = xlConditionValueNumber .Value = 0 .Operator = 7 End With With cfIconSet.IconCriteria(2) .Type = xlConditionValueNumber .Value = 60 .Operator = 7 End With With cfIconSet.IconCriteria(3) .Type = xlConditionValueNumber .Value = 70 .Operator = 7 End With With cfIconSet.IconCriteria(4) .Type = xlConditionValueNumber .Value = 80 .Operator = 7 End With With cfIconSet.IconCriteria(5) .Type = xlConditionValueNumber .Value = 90 .Operator = 7 End With End Sub

Sub CreateIconSetCF() Dim cfIconSet As IconSetCondition 'Fill cells with sample data from 1 to 10 With ActiveSheet .Range("C1") = 55 .Range("C2") = 92 .Range("C3") = 88 .Range("C4") = 77 .Range("C5") = 66 .Range("C6") = 93 .Range("C7") = 76 .Range("C8") = 80 .Range("C9") = 79 .Range("C10") = 83 .Range("C11") = 66 .Range("C12") = 74 End With Range("C1:C12").Select 'Create an icon set conditional format for the created sample data range Set cfIconSet = Selection.FormatConditions.AddIconSetCondition 'Change the icon set to a five-arrow icon set cfIconSet.IconSet = ActiveWorkbook.IconSets(xl5Arrows) 'The IconCriterion collection contains all IconCriteria 'By indexing into the collection you can modify each criterion With cfIconSet.IconCriteria(1) .Type = xlConditionValueNumber .Value = 0 .Operator = 7 End With With cfIconSet.IconCriteria(2) .Type = xlConditionValueNumber .Value = 60 .Operator = 7 End With With cfIconSet.IconCriteria(3) .Type = xlConditionValueNumber .Value = 70 .Operator = 7 End With With cfIconSet.IconCriteria(4) .Type = xlConditionValueNumber .Value = 80 .Operator = 7 End With With cfIconSet.IconCriteria(5) .Type = xlConditionValueNumber .Value = 90 .Operator = 7 End With End Sub

See also: