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)

IconCriterion class variables can be set using the IconCriteria.Item procedure.

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


Returns a Long value that specifies the number of criteria for an icon set conditional formatting rule.

The number of criteria is equivalent to the number of threshold values assigned for an icon set.

Dim lngCount As Long
lngCount = ActiveCell.FormatConditions(1).IconCriteria.Count


Returns a single IconCriterion object from the IconCriteria collection.

The value of the Index parameter cannot be greater than the number of criteria set for an icon set conditional format. The criteria are equivalent to the threshold values assigned for an icon set. To find the number of threshold values, use the Count property.

Item (Index)

Index: The index number of the IconCriterion object.

Dim icncrtItem As IconCriterion
Set icncrtItem = ActiveCell.FormatConditions(1).IconCriteria(Index:=1)


Returns or specifies the icon for a criterion in an icon set conditional formatting rule.

xlIcon0Bars - Signal Meter With No Filled Bars, xlIcon0FilledBoxes - 0 Filled Boxes, xlIcon1Bar - Signal Meter With One Filled Bar, xlIcon1FilledBox - 1 Filled Boxes, xlIcon2Bars - Signal Meter With Two Filled Bars, xlIcon2FilledBoxes - 2 Filled Boxes, xlIcon3Bars - Signal Meter With Three Filled Bars, xlIcon3FilledBoxes - 3 Filled Boxes, xlIcon4Bars - Signal Meter With Four Filled Bars, xlIcon4FilledBoxes - 4 Filled Boxes, xlIconBlackCircle - Black Circle, xlIconBlackCircleWithBorder - Black Circle With Border, xlIconCircleWithOneWhiteQuarter - Circle With One White Quarter, xlIconCircleWithThreeWhiteQuarters - Circle With Three White Quarters, xlIconCircleWithTwoWhiteQuarters - Circle With Two White Quarters, xlIconGoldStar - Gold Star, xlIconGrayCircle - Gray Circle, xlIconGrayDownArrow - Gray Down Arrow, xlIconGrayDownInclineArrow - Gray Down Incline Arrow, xlIconGraySideArrow - Gray Side Arrow, xlIconGrayUpArrow - Gray Up Arrow, xlIconGrayUpInclineArrow - Gray Up Incline Arrow, xlIconGreenCheck - Green Check, xlIconGreenCheckSymbol - Green Check Symbol, xlIconGreenCircle - Green Circle, xlIconGreenFlag - Green Flag, xlIconGreenTrafficLight - Green Traffic Light, xlIconGreenUpArrow - Green Up Arrow, xlIconGreenUpTriangle - Green Up Triangle, xlIconHalfGoldStar - Half Gold Star, xlIconNoCellIcon - No Cell Icon, xlIconPinkCircle - Pink Circle, xlIconRedCircle - Red Circle, xlIconRedCircleWithBorder - Red Circle With Border, xlIconRedCross - Red Cross, xlIconRedCrossSymbol - Red Cross Symbol, xlIconRedDiamond - Red Diamond, xlIconRedDownArrow - Red Down Arrow, xlIconRedDownTriangle - Red Down Triangle, xlIconRedFlag - Red Flag, xlIconRedTrafficLight - Red Traffic Light, xlIconSilverStar - Silver Star, xlIconWhiteCircleAllWhiteQuarters - White Circle (All White Quarters), xlIconYellowCircle - Yellow Circle, xlIconYellowDash - Yellow Dash, xlIconYellowDownInclineArrow - Yellow Down Incline Arrow, xlIconYellowExclamation - Yellow Exclamation, xlIconYellowExclamationSymbol - Yellow Exclamation Symbol, xlIconYellowFlag - Yellow Flag, xlIconYellowSideArrow - Yellow Side Arrow, xlIconYellowTrafficLight - Yellow Traffic Light, xlIconYellowTriangle - Yellow Triangle, xlIconYellowUpInclineArrow - Yellow Up Incline Arrow.

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


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


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 =


Returns one of the constants of the XlConditionValueTypes enumeration, which specifies how the threshold value for an icon set is determined.

xlConditionValueAutomaticMax - The longest data bar is proportional to the maximum value in the range, xlConditionValueAutomaticMin - The shortest data bar is proportional to the minimum value in the range, xlConditionValueFormula - Formula is used, xlConditionValueHighestValue - Highest value from the list of values, xlConditionValueLowestValue - Lowest value from the list of values, xlConditionValueNone - No conditional value, xlConditionValueNumber - Number is used, xlConditionValuePercent - Percentage is used, xlConditionValuePercentile - Percentile is used.

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


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 =