Share

Excel VBA, Fill Pattern

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

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

Jump To:

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


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:

Excel VBA, Fill Pattern Checker
You can also change the color of the pattern using the code below:

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

Result:

Excel VBA, Fill Pattern Color
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:

Excel VBA, Fill Pattern Checker, Index
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

Excel VBA, Fill Pattern Color Code


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:

Excel VBA, Color and Pattern Columns
In the figure below the user has clicked on cell A6. The pattern in cell C1 is updated:

Excel VBA, Colors and patterns, A6
In the figure below the user has selected cell B3, therefore changing the color of the pattern in cell C1 to blue:

Excel VBA, Colors and patterns, B3
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:

Excel VBA, Get Pattern
After changing the pattern in cell A2 and pressing the Run button again the values in cell B2 and C2 are updated:

Excel VBA, Get Pattern 2
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.

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com

Leave a Reply

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