generates a three-color color scale in range A1:A10:

 
Sub Add3ColorScale()
    With Range("A1:A10")
        .FormatConditions.Delete
        ' Add the Color Scale as a 3-color scale
        .FormatConditions.AddColorScale ColorScaleType:=3

        ' Format the first color as light red
        .FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValuePercent
        .FormatConditions(1).ColorScaleCriteria(1).Value = 3
        .FormatConditions(1).ColorScaleCriteria(1).FormatColor.Color = RGB(255, 0, 0)
        .FormatConditions(1).ColorScaleCriteria(1).FormatColor.TintAndShade = 0.25

        ' Format the second color as green at 50%
        .FormatConditions(1).ColorScaleCriteria(2).Type = xlConditionValuePercent
        .FormatConditions(1).ColorScaleCriteria(2).Value = 5
        .FormatConditions(1).ColorScaleCriteria(2).FormatColor.Color = RGB(0, 255, 0)
        .FormatConditions(1).ColorScaleCriteria(2).FormatColor.TintAndShade = 0

        ' Format the third color as dark blue
        .FormatConditions(1).ColorScaleCriteria(3).Type = xlConditionValuePercent
        .FormatConditions(1).ColorScaleCriteria(3).Value = 8
        .FormatConditions(1).ColorScaleCriteria(3).FormatColor.Color = RGB(0, 0, 255)
        .FormatConditions(1).ColorScaleCriteria(3).FormatColor.TintAndShade = -0.25
    End With

End Sub

1.	all the ranges that have conditional formatting set up
2.	Setting Up Conditional Formats in VBA
3.	Identifying Row with Largest Value in G
4.	Specifying an Icon Set
5.	Specifying Ranges for Each Icon
6.	Using Two Colors of Data Bars in a Range
7.	Add Crazy Icons
8.	highlight cells above average:
9.	highlight cells below average:
10.	Formatting Cells in the Top 10
11.	Formatting Cells in the Bottom 5
12.	Formatting Cells in the Top 2 percent
13.	Formatting Duplicate Cells
14.	Formatting Unique Cells
15.	Formatting Cells whose value between 10 and 20
16.	highlights all cells that contain a capital letter A
17.	highlights all dates in the past week:
18.	creates the formatting shown in column A
19.	Highlight the Entire Row for the Largest Sales Value
20.	Using the New NumberFormat Property

generates a three-color color scale in range A1:A10: : FormatConditions « Excel « VBA / Excel / Access / Word

Related examples in the same category