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:

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:

Excel VBA, Gradient Object Model
The ColorStops object contains a collection of the ColorStop object:

Excel VBA, ColorStops Class Object Model

Each ColorStop object has 2 main members:

Excel VBA, ColorStop, Object Model
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:

Excel VBA, Default Gradient Color Positions
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:
Excel VBA, Gradient, Positions
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:

Excel VBA, Gradient, First ColorStop
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:

Excel VBA, Gradient, Second ColorStop


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:

Excel VBA, Gradient, 4 Color Stops
In the figure below you can see the resulting gradient with the colors and color stop index:
Excel VBA, Gradient, Second ColorStop2

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:

Excel VBA, Fill Effects, Colors, 1, Before
After:

Excel VBA, Fill Effects, Colors, 1, After

The line below changes the second color to green:

Range("A1").Interior.Gradient.ColorStops(2).Color _
= ColorConstants.vbGreen

Result:

Excel VBA, Fill Effects, Colors, 2, 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:

Excel VBA, Fill Effects, Get Color COdes
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:

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, Gradient’s Colors”

  1. Luis says:

    Hi:

    Great explanation. Has been really very useful.

    Regards,
    Luis

Leave a Reply

Your email address will not be published. Required fields are marked *