Class ColorScaleCriterion (Excel VBA)

The class ColorScaleCriterion represents the criteria for the minimum, midpoint, or maximum thresholds for a color format conditional format.


To use a ColorScaleCriterion class variable it first needs to be instantiated, for example

Dim csc as ColorScaleCriterion
Set csc = ActiveCell.FormatConditions(1).ColorScaleCriteria(Index:=1)

ColorScaleCriterion class variables can be set using the ColorScaleCriteria.Item procedure.

For Each

Here is an example of processing the ColorScaleCriterion items in a collection.

Dim cscColorScaleCriteria As ColorScaleCriterion
For Each cscColorScaleCriteria In ActiveCell.FormatConditions(1).ColorScaleCriteria
Next cscColorScaleCriteria


Returns a Long value that specifies the number of criteria for a color scale conditional formatting rule.

The number of criteria is equivalent to the number of threshold values assigned for a color scale. This will be either two or three, depending on whether you use a two-color scale or a three-color scale for the conditional formatting rule.

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


Returns a single ColorScaleCriterion object from the ColorScaleCriteria collection.

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

Item (Index)

Index: The index number of the ColorScaleCriterion object.

Dim cscItem As ColorScaleCriterion
Set cscItem = ActiveCell.FormatConditions(1).ColorScaleCriteria(Index:=1)


Returns a FormatColor object, which specifies the color assigned to the threshold of a color scale conditional format.

You can set the color of the minimum, midpoint, or maximum thresholds to any custom color by using the Color property or the ThemeColor property of the FormatColor object.

Dim fcrFormatColor As FormatColor
Set fcrFormatColor = ActiveCell.FormatConditions(1).ColorScaleCriteria(1).FormatColor


Returns a Long value indicating which threshold the criteria represents.

For a two-color scale conditional formatting rule, this property will return a value of "1" for the minimum threshold and "2" for the maximum threshold. When using a three-color scale rule, the values will be "1" for the minimum, "2" for the midpoint, and "3" for the maximum thresholds.

Dim lngIndex As Long
lngIndex = ActiveCell.FormatConditions(1).ColorScaleCriteria(1).Index


Returns one of the constants of the XlConditionValueTypes enumeration, which specifies how the threshold values for a data bar or color scale conditional format are 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 a data bar or color scale can be a number, percent, formula, or percentile. Setting the type to percentile will use the Percentile function in Microsoft Excel to determine the threshold value.

ActiveCell.FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValueAutomaticMax


Returns or sets the minimum, midpoint, or maximum threshold value for a color scale 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).ColorScaleCriteria(1).Value =