The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

ColorScale Object

Office 2007
Represents a color scale conditional formatting rule.

Version Information
 Version Added:  Excel 2007


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.


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 ColorScale
    '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
    '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

Community Additions