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
• Modify Existing Gradients Color for Range

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:

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”:

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:

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:

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:

Note: Intellisense may cease to display after .gradient.

See also:

• Excel VBA, Gradient’s Colors

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