Excel-VBA gradient colorstops

Question

What is the best way to apply horizontal gradient fill effect to a cell through macro code?

I've set the desired gradient in Excel (right-click on cell B1, Format Cells..., Fill, Fill Effects, Two Colors, Horizontal, "Okay" all).

I then have the following code to find out how to represent this via code. When I step through the code, I can use the locals window to inspect the gradient and colorstops of the myrange object:

Dim myrange As range
Set myrange = ActiveSheet.range("B1")

Using this information, I can now hard-code the information in a macro, in hopes of duplicating the gradient fill by code:

'First, delete any previous gradient colorstops
For Each cs In myrange.Interior.Gradient.ColorStops
  cs.Delete
Next

'Then, assign the desired colorstops in the gradient
With myrange.Interior.Gradient.ColorStops
  .add color = 16777215
  Position = 0
  ThemeColor = 1
  TintAndShade = 0

  .add color = 7961087
  Position = 0.5
  ThemeColor = 0
  TintAndShade = 0

  .add color = 16777215
  Position = 1
  ThemeColor = 1
  TintAndShade = 0

End With

Unfortunately, this results in something that looks totally wrong. The most obvious thing that's wrong is that the gradient is in black and white, even as I adjust the RGB values.

Is there something else that should be added here?

have you tried using macro recorder just to see how it does it? For complex formatting and so on like this I often start there to see the properties needed then insert certain bits of it into my own 'clean' code. — Cor_Blimey
– Cor_Blimey, Commented Apr 28, 2014 at 19:42

teylyn · Accepted Answer · 2014-04-29 01:12:44Z

7

The assignment of ColorStops is not valid code. You need to add the colorstops and then set their properties. The macro recorder does it correctly.

Sub SetGradient()
Dim myrange As Range
Set myrange = ThisWorkbook.Sheets("Sheet1").Range("B1")

    With myrange.Interior
        .Pattern = xlPatternLinearGradient
        .Gradient.Degree = 90
        .Gradient.ColorStops.Clear
    End With
    With myrange.Interior.Gradient.ColorStops.Add(0)
        .Color = 16777215
        .TintAndShade = 0
    End With
    With myrange.Interior.Gradient.ColorStops.Add(0.5)
        .Color = 7961087
        .TintAndShade = 0
    End With
    With myrange.Interior.Gradient.ColorStops.Add(1)
        .Color = 16777215
        .TintAndShade = 0
    End With
End Sub

answered Apr 29, 2014 at 1:12

teylyn

36.2k6 gold badges58 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aaron Thomas Over a year ago

Good advice, and it works perfectly. The good thing about this approach is that I can capture other fill effects and incorporate those into my code also. Thank you.

Collectives™ on Stack Overflow

Excel-VBA gradient colorstops

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related