(Solved) – How to do conditional formatting of colour scale in VBA in a more efficient way?


As i wanted to conditional formatting of colour scale in the range as shown below, i recorded the macro while doing it. The code will work but i encountered the “procedure is large error”(compile error) when i do the same thing for 36 pivottables. So is there a way to decrease the size of the procedure so that i can do it for 60 pivotables?

The type of conditional formatting that i used for my colour scale

enter image description here

enter image description here

Sub test()

    Selection.FormatConditions.AddColorScale ColorScaleType:=3
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 7039480
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 8711167
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 8109667
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ScopeType = xlSelectionScope
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *