Set gradient using VBA

[YasserKhalil]

Hello everyone
I have recorded a macro to set the gradient for cell

Code: Select all

Sub Macro1()
'
' Macro1 Macro
'

'
    With Selection.Interior
        .Pattern = xlPatternRectangularGradient
        .Gradient.RectangleLeft = 0.5
        .Gradient.RectangleRight = 0.5
        .Gradient.RectangleTop = 0.5
        .Gradient.RectangleBottom = 0.5
        .Gradient.ColorStops.Clear
    End With
    With Selection.Interior.Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.Interior.Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
    End With
End Sub

How can I abbreviate such a code? What is the most important parts?

[HansV]

Here is a slightly shorter version:

Code: Select all

Sub Macro1()
    With Selection.Interior
        .Pattern = xlPatternRectangularGradient
        With .Gradient
            .RectangleLeft = 0.5
            .RectangleRight = 0.5
            .RectangleTop = 0.5
            .RectangleBottom = 0.5
            With .ColorStops
                .Clear
                .Add(0).ThemeColor = xlThemeColorDark1
                .Add(1).ThemeColor = xlThemeColorLight1
            End With
        End With
    End With
End Sub

And the minimum:

Code: Select all

Sub Macro2()
    With Selection.Interior
        .Pattern = xlPatternRectangularGradient
        With .Gradient
            .RectangleLeft = 0.5
            .RectangleRight = 0.5
            .RectangleTop = 0.5
            .RectangleBottom = 0.5
            .ColorStops(1).ThemeColor = xlThemeColorDark1
            .ColorStops(2).ThemeColor = xlThemeColorLight1
        End With
    End With
End Sub

(This uses the default ColorStops at positions 1 and 2)

[YasserKhalil]

Thank you very much Mr. Hans
Best Regards