Excel VBA Range Gradient Color

In the article Excel VBA, Gradient’s Colors I’ve explained the basics about working with gradient colors. I’m assuming readers are familiar with the concepts explained in that article before reading this article.

Jump To:


Gradient Color, Range:

In the article Excel VBA, Gradient’s Colors I provided 2 examples for creating gradients with different colors. The examples created a gradient with multiple colors for cell A1. In this example I will apply the gradient used in example 2 in that article to a range of cells:

Sub Example()
Dim objColorStop As ColorStop

Range("B2:D4").Interior.Pattern = xlPatternLinearGradient
'changes its orientation
Range("B2:D4").Interior.Gradient.Degree = 90
'clears the previous colostop objects
Range("B2:D4").Interior.Gradient.ColorStops.Clear

Set objColorStop = Range("B2:D4").Interior.Gradient.ColorStops.Add(0)
objColorStop.Color = vbYellow

Set objColorStop = Range("B2:D4").Interior.Gradient.ColorStops.Add(0.33)
objColorStop.Color = vbRed

Set objColorStop = Range("B2:D4").Interior.Gradient.ColorStops.Add(0.66)
objColorStop.Color = vbGreen

Set objColorStop = Range("B2:D4").Interior.Gradient.ColorStops.Add(1)
objColorStop.Color = vbBlue
End Sub

Result:

Excel VBA, Range, Gradient Color


Modify Existing Gradients Color for Range:

There might be a situation where there is already an existing gradient. In the figure below there is a gradient in the range “A1:F9”:

Excel VBA, Existing Gradient Range

Lets say we only want to change the gradient’s color in the range “B2:E2”. We want the color at the position 0 to be red and the color at the position 1 to be green:

Color Stop Color
0
1

One option would be to apply the code used in the previous section:

Sub Example()
Dim objColorStop As ColorStop

Range("B2:E2").Interior.Pattern = xlPatternLinearGradient
'changes its orientation
Range("B2:E2").Interior.Gradient.Degree = 90
'clears the previous colostop objects
Range("B2:E2").Interior.Gradient.ColorStops.Clear

Set objColorStop = Range("B2:E2").Interior.Gradient.ColorStops.Add(0)
objColorStop.Color = vbRed

Set objColorStop = Range("B2:E2").Interior.Gradient.ColorStops.Add(1)
objColorStop.Color = vbGreen
End Sub

Result:

Excel VBA, Existing Gradient Range Modify Option 1

As can be seen from the figure above the problem with this approach is that the rest of the formatting options applied to the gradient is overwritten with the default settings. One method for preventing this error, is to get all the previous formattings applied to the range and apply them to the new gradient. The previous formatting are:

  • The gradient pattern
  • The gradient rotation

Sub Example()
Dim objColorStop As ColorStop
Dim lngPattern As Long
Dim lngDegree As Long

'gets the pattern used
lngPattern = Range("B2").Interior.Pattern
'gets the rotation used
lngDegree = Range("B2").Interior.Gradient.Degree
Range("B2:E2").Interior.Pattern = lngPattern
'changes its orientation
Range("B2:E2").Interior.Gradient.Degree = lngDegree
'clears the previous colostop objects
Range("B2:E2").Interior.Gradient.ColorStops.Clear

Set objColorStop = Range("B2:E2").Interior.Gradient.ColorStops.Add(0)
objColorStop.Color = vbRed

Set objColorStop = Range("B2:E2").Interior.Gradient.ColorStops.Add(1)
objColorStop.Color = vbGreen
End Sub

Result:

Excel VBA, Existing Gradient Range Modify Option 2

Note: Intellisense may cease to display after .gradient.

 

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 *