Formatting Cells Fill with VBA

Background Color

You can use any of the following methods:

The following example sets the background color of range A1:D1 to RGB(0, 0, 0) (black).

Range("A1:D1").Interior.Color = RGB(0, 0, 0)

The following example sets the background color of range A1:D1 to 1.

Range("A1:D1").Interior.ColorIndex = 1

The following example sets the background color of range A1:D1 to xlThemeColorDark2.

Range("A1:D1").Interior.ThemeColor = xlThemeColorDark2

Fill Effects

Horizontal

The colors blend from top to bottom.

With Range("A2").Interior
    .Pattern = xlPatternLinearGradient
    .Gradient.Degree = 90
    .Gradient.ColorStops.Clear
    With .Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
    End With
End With

With Range("A3").Interior
    .Pattern = xlPatternLinearGradient
    .Gradient.Degree = 270
    .Gradient.ColorStops.Clear
    With .Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
    End With
End With

With Range("A4").Interior
    .Pattern = xlPatternLinearGradient
    .Gradient.Degree = 90
    .Gradient.ColorStops.Clear
    With .Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .Gradient.ColorStops.Add(0.5)
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
    End With
    With .Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
End With

Vertical

The colors blend from side to side.

With Range("B2").Interior
    .Pattern = xlPatternLinearGradient
    .Gradient.Degree = 0
    .Gradient.ColorStops.Clear
    With .Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
    End With
End With

With Range("B3").Interior
    .Pattern = xlPatternLinearGradient
    .Gradient.Degree = 270
    .Gradient.ColorStops.Clear
    With .Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
    End With
End With

With Range("B4").Interior
    .Pattern = xlPatternLinearGradient
    .Gradient.Degree = 90
    .Gradient.ColorStops.Clear
    With .Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .Gradient.ColorStops.Add(0.5)
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
    End With
    With .Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
End With

Diagonal up

The colors blend at a diagonal angle from the upper left corner to the bottom right corner.

With Range("C2").Interior
    .Pattern = xlPatternLinearGradient
    .Gradient.Degree = 45
    .Gradient.ColorStops.Clear
    With .Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
    End With
End With

With Range("C3").Interior
    .Pattern = xlPatternLinearGradient
    .Gradient.Degree = 225
    .Gradient.ColorStops.Clear
    With .Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
    End With
End With

With Range("C4").Interior
    .Pattern = xlPatternLinearGradient
    .Gradient.Degree = 45
    .Gradient.ColorStops.Clear

    With .Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .Gradient.ColorStops.Add(0.5)
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
    End With
    With .Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
End With

Diagonal down

The colors blend at a diagonal angle from the upper right corner to the bottom left corner.

With Range("D2").Interior
    .Pattern = xlPatternLinearGradient
    .Gradient.Degree = 135
    .Gradient.ColorStops.Clear
    With .Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
    End With
End With

With Range("D3").Interior
    .Pattern = xlPatternLinearGradient
    .Gradient.Degree = 315
    .Gradient.ColorStops.Clear
    With .Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
    End With
End With

With Range("D4").Interior
    .Pattern = xlPatternLinearGradient
    .Gradient.Degree = 135
    .Gradient.ColorStops.Clear
    With .Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .Gradient.ColorStops.Add(0.5)
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
    End With
    With .Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
End With

From corner

The colors blend from one of the corners towards the center.

With Range("E2").Interior
    .Pattern = xlPatternRectangularGradient
    .Gradient.RectangleLeft = 0
    .Gradient.RectangleRight = 0
    .Gradient.RectangleTop = 0
    .Gradient.RectangleBottom = 0
    .Gradient.ColorStops.Clear
    With .Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
    End With
End With

With Range("E3").Interior
    .Pattern = xlPatternRectangularGradient
    .Gradient.RectangleLeft = 1
    .Gradient.RectangleRight = 1
    .Gradient.RectangleTop = 0
    .Gradient.RectangleBottom = 0
    .Gradient.ColorStops.Clear
    With .Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
    End With
End With

With Range("E4").Interior
    .Pattern = xlPatternRectangularGradient
    .Gradient.RectangleLeft = 0
    .Gradient.RectangleRight = 0
    .Gradient.RectangleTop = 1
    .Gradient.RectangleBottom = 1
    .Gradient.ColorStops.Clear
    With .Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
    End With
End With

With Range("E5").Interior
    .Pattern = xlPatternRectangularGradient
    .Gradient.RectangleLeft = 1
    .Gradient.RectangleRight = 1
    .Gradient.RectangleTop = 1
    .Gradient.RectangleBottom = 1
    .Gradient.ColorStops.Clear
    With .Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
    End With
End With

From center

The colors blend from the center out towards the four corners.

With Range("F2").Interior
    .Pattern = xlPatternRectangularGradient
    .Gradient.RectangleLeft = 0.5
    .Gradient.RectangleRight = 0.5
    .Gradient.RectangleTop = 0.5
    .Gradient.RectangleBottom = 0.5
    .Gradient.ColorStops.Clear
    With .Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
    End With
End With

Pattern Color

You can use any of the following methods:

The following example sets the color of the interior pattern for rectangle one on Sheet1

With Worksheets("Sheet1").Rectangles(1).Interior
    .Pattern = xlGrid
    .PatternColor = RGB(255, 0, 0)
End With

The following example sets the color of the interior pattern for rectangle one on Sheet1.

With Worksheets("Sheet1").Rectangles(1).Interior
    .Pattern = xlChecker
    .PatternColorIndex = 5
End With

The following example sets the color of the interior pattern for Range A1:D4 one on Sheet1

With Worksheets("Sheet1").Range("A1:D4").Interior
    .Pattern = xlGrid
    .PatternThemeColor = 2
End With

Pattern Style

The value of Interior Pattern property can be set to one of the XlPattern constants:

Name	Value	Description
xlPatternAutomatic	-4105	Excel controls the pattern.
xlPatternChecker	9	Checkerboard.
xlPatternCrissCross	16	Criss-cross lines.
xlPatternDown	-4121	Dark diagonal lines running from the upper-left to the lower-right.
xlPatternGray16	17	16% gray.
xlPatternGray25	-4124	25% gray.
xlPatternGray50	-4125	50% gray.
xlPatternGray75	-4126	75% gray.
xlPatternGray8	18	8% gray.
xlPatternGrid	15	Grid.
xlPatternHorizontal	-4128	Dark horizontal lines.
xlPatternLightDown	13	Light diagonal lines running from the upper-left to the lower-right.
xlPatternLightHorizontal	11	Light horizontal lines.
xlPatternLightUp	14	Light diagonal lines running from the lower-left to the upper-right.
xlPatternLightVertical	12	Light vertical bars.
xlPatternNone	-4142	No pattern.
xlPatternSemiGray75	10	75% dark gray.
xlPatternSolid	1	Solid color.
xlPatternUp	-4162	Dark diagonal lines running from the lower-left to the upper-right.
xlPatternVertical	-4166	Dark vertical bars.

The following example adds a crisscross pattern to the interior of cell A1.

Range("A1").Interior.Pattern = xlPatternCrissCross