Class IconCriterion (Excel VBA)
The class IconCriterion represents the criterion for an individual icon in an icon set. The criterion specifies the range of values and the threshold type associated with the icon in an icon set conditional formatting rule. To use a IconCriterion class variable it first needs to be instantiated, for example
Dim icncrt as IconCriterion
Set icncrt = ActiveCell.FormatConditions(1).IconCriteria(Index:=1)
For Each
Here is an example of processing the IconCriterion items in a collection.
Dim icncrtIconCriteria As IconCriterion
For Each icncrtIconCriteria In ActiveCell.FormatConditions(1).IconCriteria
Next icncrtIconCriteria
Icon
Returns or specifies the icon for a criterion in an icon set conditional formatting rule. Here you can find possible values for
After you set the Icon property for the icon criterion in an icon set conditional formatting rule, the IconSet property of the IconSetCondition object is changed to xlCustomSet.
ActiveCell.FormatConditions(1).IconCriteria(1).Icon = xlIcon0Bars
Index
Returns a Long value indicating which threshold the criteria represents.
For an icon set, this property will return a value of 1 for the minimum threshold, 2 for the next threshold, and so on.
Dim lngIndex As Long
lngIndex = ActiveCell.FormatConditions(1).IconCriteria(1).Index
Operator
Returns or sets one of the constants of the XlFormatConditionOperator enumeration, which specifies if the threshold is "greater than" or "greater than or equal to" the threshold value.
For an icon set conditional formatting rule, this property can be set to only one of the following two constants: xlGreater or xlGreaterEqual.
ActiveCell.FormatConditions(1).IconCriteria(1).Operator =
Type
Returns one of the constants of the XlConditionValueTypes enumeration, which specifies how the threshold value for an icon set is determined. Here you can find possible values for
The type of threshold value for an icon set can be a number, percent, formula, or percentile. Setting the type to percentile will use the Percentile function in Excel to determine the threshold value.
ActiveCell.FormatConditions(1).IconCriteria(1).Type = xlConditionValueAutomaticMax
Value
Returns or sets the threshold value for an icon in a conditional format. Read/write Variant.
You can set the value only if the Type property for the conditional format is set to one of the following XlConditionValueTypes constants: xlConditionValueNumber, xlConditionValuePercent, xlConditionValuePercentile, or xlConditionValueFormula. If the type of threshold is a formula, you can set the formula as a String. The formula must return a single number.
ActiveCell.FormatConditions(1).IconCriteria(1).Value =