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:
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:
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:
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:
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
One thought on “Excel VBA Range Gradient Color”