ColorScale Object

  • Article
Excel Developer Reference

Represents a color scale conditional formatting rule.

Version Information
 Version Added:  Excel 2007

Remarks

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

Color scales are visual guides that help you understand data distribution and variation. You can apply either a two-color or a three-color scale to a range of data, data in a table, or data in a PivotTable report. For a two-color scale conditional format, you assign the value, type, and color to the minimum and maximum thresholds of a range. A three-color scale also has a midpoint threshold.

Each of these thresholds is determined by setting the properties of the ColorScaleCriteria object. The ColorScaleCriteria object, which is a child of the ColorScale object, is a collection of all of the ColorScaleCriterion objects for the color scale.

Example

The following code example creates a range of numbers and then applies a two-color scale conditional formatting rule to that range. The color for the minimum threshold is then assigned to red and the maximum threshold to blue.

Visual Basic for Applications 
  Sub CreateColorScaleCF()

Dim cfColorScale As <strong>ColorScale</strong>

'Fill cells with sample data from 1 to 10
With ActiveSheet
    .Range("C1") = 1
    .Range("C2") = 2
    .Range("C1:C2").AutoFill Destination:=Range("C1:C10")
End With

Range("C1:C10").Select

'Create a two-color ColorScale object for the created sample data range
Set cfColorScale = Selection.FormatConditions.AddColorScale(ColorScaleType:=2)

'Set the minimum threshold to red and maximum threshold to blue
cfColorScale.ColorScaleCriteria(1).FormatColor.Color = RGB(255, 0, 0)
cfColorScale.ColorScaleCriteria(2).FormatColor.Color = RGB(0, 0, 255)
    



End Sub

See Also