Excel VBA - Apply Colour Scale Conditional Formatting with VBA Macro

To add Colour Scales with conditional formatting use the AddColorScale method of the FormatConditions object. The AddColorScale method has one parameter – ColorScaleType which should be set to 2 or 3 depending on whether you want 2 or 3 colours on your scale.

You can the use the ColorScaleCriteria Property to specify colour and settings for each part on the scale, ColorScaleCriteria(1) for the first colour on the scale, ColorScaleCriteria(2) for the second, ColorScaleCriteria(3) for the third.

For each ColorScaleCriteria use FormatColor.Color to specify colour and Type to specify which values apply to which part of the scale – this can be set to automatic, by percentage or by value, just like in Excel (see below).

XlConditionValueTypes
xlConditionValueAutomaticMax
xlConditionValueAutomaticMin
xlConditionValueFormula
xlConditionValueHighestValue
xlConditionValueLowestValue
xlConditionValueNone
xlConditionValueNumber
xlConditionValuePercent
xlConditionValuePercentile

Sub colourscales()
Dim rg As Range
Dim cs As ColorScale
Set rg = Range("W6", Range("W6").End(xlDown))
rg.FormatConditions.Delete
'colour scale will have three colours
Set cs = rg.FormatConditions.AddColorScale(ColorScaleType:=3)
With cs
    'the first colour is blue
    With .ColorScaleCriteria(1)
        .FormatColor.Color = RGB(102, 153, 255)
        .Type = xlConditionValueLowestValue
    End With
    'the second colour is yellow set at value 18
    With .ColorScaleCriteria(2)
        .FormatColor.Color = RGB(255, 230, 153)
        .Type = xlConditionValueNumber
        .Value = 18
    End With
    'the third colour is red
    With .ColorScaleCriteria(3)
        .FormatColor.Color = RGB(255, 51, 0)
        .Type = xlConditionValueHighestValue
    End With
End With
End Sub

The code above relates to the date shown below.

Excel VBA – Apply Colour Scale Conditional Formatting with VBA Macro