Excel VBA, Gradient’s Colors
This article explains how you can change a gradient’s first and second color using VBA for Excel. It also provides a sample for getting the current gradient color used in a cell. Note the codes in this article assume a gradient already exists and we are only trying to modify its colors. Also note that each gradient has 2 colors, color1 and color2.
Jump To:
- Working With Gradients
- Example 1, Create Gradient With Different Colors
- Example 2, Multiple ColorStop Objects
Contents
Working With Gradients:
Please note that the codes below only change the gradients color. Therefore it is assumed that a gradient already exists in the cell under question. The article Excel VBA, Create Gradient explains how you can create a gradient using VBA.
A range object has the following object model:
The ColorStops object contains a collection of the ColorStop object:
Each ColorStop object has 2 main members:
Position: A double value between 0~1.
Color: The color associate with that position.
When you create a gradient, whether using VBA or using the gradient dialogs by default the gradient’s ColorStops collection has 2 ColorStop objects. One of the color stop objects has the position 1 and the other has the position 2:
In order to be able to fully use the gradient properties in VBA, I’d recommend changing the default positions to 0 and 1. this way we would be able to add position in between (i.e 0.5, 0.3) which is explained in the example below:
The code below creates a gradient in cell A1 and changes the default positions to 0 and 1:
Sub main()
Dim objColorStop As ColorStop
Dim lngColor1 As Long
Dim lngColor0 As Long
'creates the gradient in cell A1
Range("A1").Interior.Pattern = xlPatternLinearGradient
'changes its orientation
Range("A1").Interior.Gradient.Degree = 90
'gets the color code for the first colorstop object
lngColor0 = Range("A1").Interior.Gradient.ColorStops(1).Color
'gets the color code for the second colorstop object
lngColor1 = Range("A1").Interior.Gradient.ColorStops(2).Color
'clears the previous colostop objects
Range("A1").Interior.Gradient.ColorStops.Clear
'creates a colorstop object with the position 0
Set objColorStop = Range("A1").Interior.Gradient.ColorStops.Add(0)
'changes its color to the first color
objColorStop.Color = lngColor0
'creates a colorstop object with the position 1
Set objColorStop = Range("A1").Interior.Gradient.ColorStops.Add(1)
'changes its color to the second color
objColorStop.Color = lngColor1
End Sub
The code below creates the gradient. Note if a gradient already exists in cell A1 there would be no need for this line:
'creates the gradient in cell A1
Range("A1").Interior.Pattern = xlPatternLinearGradient
'changes its orientation
Range("A1").Interior.Gradient.Degree = 90
The code below gets the color codes for the first and second color used in the gradient. For more information about working with colors in VBA for Excel please see VBA Excel, Colors:
'gets the color code for the first colorstop object
lngColor0 = Range("A1").Interior.Gradient.ColorStops(1).Color
'gets the color code for the second colorstop object
lngColor1 = Range("A1").Interior.Gradient.ColorStops(2).Color
The line below removes the previous color stop objects:
Range("A1").Interior.Gradient.ColorStops.Clear
The line below creates a colorstop object at the position 0:
Set objColorStop = Range("A1").Interior.Gradient.ColorStops.Add(0)
The line below changes the new ColorStop objects color to the first color used in the initial gradient:
objColorStop.Color = lngColor0
Example 1, Create Gradient With Different Colors:
In this example we will create a gradient in cell A1 and change the first color to green:
Sub Example1_a()
Dim objColorStop As ColorStop
Dim lngColor1 As Long
'creates the gradient in cell A1
Range("A1").Interior.Pattern = xlPatternLinearGradient
'changes its orientation
Range("A1").Interior.Gradient.Degree = 90
'gets the color code for the second colorstop object
lngColor1 = Range("A1").Interior.Gradient.ColorStops(2).Color
'clears the previous colostop objects
Range("A1").Interior.Gradient.ColorStops.Clear
'creates a colorstop object with the position 0
Set objColorStop = Range("A1").Interior.Gradient.ColorStops.Add(0)
'changes its color to green
objColorStop.Color = vbGreen
'creates a colorstop object with the position 1
Set objColorStop = Range("A1").Interior.Gradient.ColorStops.Add(1)
'changes its color to red
objColorStop.Color = lngColor1
End Sub
The highlighted line changes the first color to green. Result:
We have also changed the second color to red:
Sub Example1_b()
Dim objColorStop As ColorStop
'creates the gradient in cell A1
Range("A1").Interior.Pattern = xlPatternLinearGradient
'changes its orientation
Range("A1").Interior.Gradient.Degree = 90
'clears the previous colostop objects
Range("A1").Interior.Gradient.ColorStops.Clear
'creates a colorstop object with the position 0
Set objColorStop = Range("A1").Interior.Gradient.ColorStops.Add(0)
'changes its color to green
objColorStop.Color = vbGreen
'creates a colorstop object with the position 1
Set objColorStop = Range("A1").Interior.Gradient.ColorStops.Add(1)
'changes its color to red
objColorStop.Color = vbRed
End Sub
The highlighted line changes the second color to red:
Example 2, Multiple ColorStop Objects:
In the previous example there was only 2 ColorStop objects. We can add as many ColorStop objects as we need. In the example below 4 ColorStop objects have been used:
| Color Stop | Color |
|---|---|
| 0 | |
| 0.33 | |
| 0.66 | |
| 1 |
The default ColorStop positions has been explained in the previous section. The code below will change the color at the position “0” to green. That means the white color will be replaced with green:
Sub Example2()
Dim objColorStop As ColorStop
Dim lngColor1 As Long
'creates the gradient in cell A1
Range("A1").Interior.Pattern = xlPatternLinearGradient
'changes its orientation
Range("A1").Interior.Gradient.Degree = 90
'clears the previous colostop objects
Range("A1").Interior.Gradient.ColorStops.Clear
'Creates the color stops for the gradient in cell A1
Set objColorStop = Range("A1").Interior.Gradient.ColorStops.Add(0)
objColorStop.Color = vbYellow
Set objColorStop = Range("A1").Interior.Gradient.ColorStops.Add(0.33)
objColorStop.Color = vbRed
Set objColorStop = Range("A1").Interior.Gradient.ColorStops.Add(0.66)
objColorStop.Color = vbGreen
Set objColorStop = Range("A1").Interior.Gradient.ColorStops.Add(1)
objColorStop.Color = vbBlue
End Sub
Result:
In the figure below you can see the resulting gradient with the colors and color stop index:
The line below changes the gradient’s first color to red. For more information about working with colors please see VBA Excel Colors:
Range("A1").Interior.Gradient.ColorStops(1).Color _
= ColorConstants.vbRed
Before:
After:
The line below changes the second color to green:
Range("A1").Interior.Gradient.ColorStops(2).Color _
= ColorConstants.vbGreen
Result:
The code below gets the color codes used for the first and second gradient colors and prints them in cells B2 and C2. For an example on working with color codes please see Excel VBA, Color Code:
Cells(2, 2) = _
Range("A1").Interior.Gradient.ColorStops(1).Color
Cells(2, 3) = _
Range("A1").Interior.Gradient.ColorStops(2).Color
Result:
Note: Intellisense may cease to display after .gradient.
Note: In this section It is assumed that a gradient has already been created for cell A1. We are just trying to modify it. For more information about creating gradients please see Excel VBA, Create Gradient.
See also:
- Excel VBA, Fill Effects, Gradient
- Excel VBA, Formatting Cells and Ranges Using the Macro Recorder
- Excel VBA, Color Code
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
2 thoughts on “Excel VBA, Gradient’s Colors”