Command for Fill Effect in Excel VBA

Question

Friends, I’m using the tip, described in the "Excel Guru" script in Creating Interactive Maps in Excel:

Sub ColorirMapa()

Dim Microareas As Range 
Dim Celula As String 

For Each Microareas In Range (“Microareas”)
Celula = Cells (Microareas.Row, 4)
ActiveSheet.Shapes(Microareas).Fill.ForeColor.RGB = Range(Celula).Interior.Color
Next Regiao

End Sub

What would be the command in VBA (Visual Basic for Applications) to put texture dotted, striped or other type fill effect - in place of colors?

Answer 1

You would need to enter the following properties of Interior of your object Cell to have the desired effect:

Range(Celula).Select

With Selection.Interior
    .Pattern = xlVertical
    .PatternColorIndex = xlAutomatic
    .Color = 5296274
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With

It would result in something like this:

Golden tip

Often we want to turn into VBA code formatting, lists, freeze columns but we have no idea where to start. Excel has a feature that lets you record the actions you perform and turns into VBA codes for you.

Although the generated code is not ideal, it meets most of the cases and also gives you an idea of how to do what you want.

1. Enable your Excel "Developer" tab, in File > Options > Customize Range of Options.

2. Select the Record macro option

3. Do what you want in Excel. Finished what you needed to do, stop the macro execution and analyze the generated code