Excel VBA, Fill Pattern

Sep 09, 2015 by azurous in Color

Various different patterns can be applied to cells in Excel. These patterns can be applied manually using the Format Cell dialog:

This article will explain how you can apply different patterns to cells using VBA for Excel.

Jump To:

Basics
Example 1, Set Pattern
Example 2, Get Pattern

You can download the file and code related to this article here.

–

Contents

Basics:

The pattern of a cell can be changed using the xlPattern enumeration. The code below changes the pattern of cell A1 to the checker pattern:

range("A1").Interior.Pattern = XlPattern.xlPatternChecker

Result:

You can also change the color of the pattern using the code below:

Range("A1").Interior.PatternColor = vbBlue

Result:

For more information about colors please see Excel VBA Colors.

You can also get the index of the current pattern used in a cell using the code below. The code below prints the enumeration index of the pattern used in cell A1 in cell B1:

You can also get the color code used in a pattern using the code below. The code below prints the color code of the color used for the pattern in cell A1 in cell C1:

Cells(1, 3) = Range("A1").Interior.PatternColor

–

Example 1, Set Pattern:

In the example below there are a list of patterns in column A, and a list of colors in column B. By clicking these cells the fill pattern of cell C1 is updated.

In the figure below you can see the pattern and color columns:

In the figure below the user has clicked on cell A6. The pattern in cell C1 is updated:

In the figure below the user has selected cell B3, therefore changing the color of the pattern in cell C1 to blue:

The main function for the program is a Worksheet_SelectionChange event handler. The event handler executes when the user selects a new cell:

'executes when the user selects a new cell Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'checks if the selected cell is in column A If Target.Column = 1 Then 'checks if the selected cell is between row 2 and 'the last row in column A If Target.Row >= 2 And Target.Row <= 19 Then 'adjusts the pattern of cell C1 to the selected pattern 'in column A Range("C1").Interior.Pattern = Target.Interior.Pattern End If 'checks if the selected cell is in column B ElseIf Target.Column = 2 Then 'checks if the selected cell is between row 2 and 'the last row in column B If Target.Row >= 2 And Target.Row <= 9 Then 'changes the pattern color of cell C1 Range("C1").Interior.PatternColor = Target.Interior.Color End If End If End Sub

The If statement checks if the selected cell is in column A or column B. Column A would change the pattern while column B would only change the pattern color. Note that Target is the input parameter to the event handler Worksheet_SelectionChange. It determines the range selected by the user:

If Target.Column = 1 Then ... ElseIf Target.Column = 2 Then ... End If

The If statements below check if the selected cell is between the first and last row of its respective column:

If Target.Row >= 2 And Target.Row <= 19 Then ... End If

If Target.Row >= 2 And Target.Row <= 9 Then ... End If

The line below changes the pattern of cell C1 to the pattern selected in column A:

Range("C1").Interior.Pattern = Target.Interior.Pattern

The line below changes the pattern color of cell C1 to the color selected by the user in column B:

Range("C1").Interior.PatternColor = Target.Interior.Color

–

Example 2, Get Pattern:

In this example the user manually creates a fill pattern for cell A2. After pressing the run button the selected pattern and pattern color are printed in cells B2 and C2:

After changing the pattern in cell A2 and pressing the Run button again the values in cell B2 and C2 are updated:

The program uses a Button_Click event handler. The event handler executes when the user presses the Run button:

Private Sub btnRun_Click() 'gets the pattern index of cell A2 Cells(2, 2) = Range("A2").Interior.Pattern 'gets the color index of cell A2 Cells(2, 3) = Range("A2").Interior.PatternColor End Sub

The line below gets the pattern index of cell A2:

Cells(2, 2) = Range("A2").Interior.Pattern

The line below gets the color code of the color of the pattern in cell A2:

Cells(2, 3) = Range("A2").Interior.PatternColor

You can download the file and code related to this article here.