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)
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
FormatColor
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
Index
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
Type
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. Here you can find possible values for
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
Value
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 =