Excel VBA, Gradient, Rotation

In this article I will provide an example on how you can apply rotations to gradients.

Jump To:


Example 1, Basics:

In the example below the user selects a degree in cell B3, the rotation of the gradient in cell A1 is changed accordingly:

Excel, VBA, Gradient, Rotation
In the example below the user selects 135 degrees:

Excel, VBA, Gradient, Rotation 135
The main function for this code is a worksheet_change event handler. The event handler executes when the user makes changes to the sheet, in other words when the user enters a new value in cell B3:

'executes when the user makes changes to the sheet
Private Sub worksheet_change(ByVal target As Range)
'creates a gradient for cell A1
Range("A1").Interior.Pattern = xlPatternLinearGradient
'changes the gradients rotation to the value input in cell
'B3
Range("A1").Interior.Gradient.Degree = Cells(3, 2)
End Sub

The first line creates a gradient. this is not needed if you are sure a gradient already exists in the cell. If a gradient doesn’t exist in cell A1 and we try to execute the next line, an error wi occur:

Range("A1").Interior.Pattern = xlPatternLinearGradient

The line below adjusts the gradient’s rotation:

Range("A1").Interior.Gradient.Degree = Cells(3, 2)


Example 2, Preserve Colors:

In the previous example if cell A1 had different colors, the code above would remove the colors.

Before:

Excel, VBA, Gradient, Rotation Before
After:

Excel, VBA, Gradient, Rotation After
If you want to preserve the gradient color while changing the gradients rotation you can use the code below:

'executes when the user makes changes to the sheet
Private Sub worksheet_change(ByVal target As Range)
'changes the gradients rotation to the value input in cell
'B3
Range("A1").Interior.Gradient.Degree = Cells(3, 2)
End Sub

please note the code above will cause an error if a gradient doesn’t already exist in cell A1. In order to prevent such an error you could add an event handler:

'executes when the user makes changes to the sheet
Private Sub worksheet_change(ByVal target As Range)
'changes the gradients rotation to the value input in cell
'B3
On Error GoTo lblNoGradient:
Range("A1").Interior.Gradient.Degree = Cells(3, 2)
Exit Sub

lblNoGradient:
MsgBox ("There is no gradient in cell A1")
End Sub

The message box below will display if a gradient doesn’t exist in cell A1:

Excel VBA, No Gradient Error


Apply Gradient Rotation to Range:

You can apply a rotational degree to the gradient of a range of cells using the code below:

Range("A1:C4").Interior.Gradient.Degree = 143

please note the code above only works if the following conditions exist:

  • A gradient already exists in the range “A1:C4”
  • The gradient in the range “A1:C4” has the same properties (colors, rotations, …)

Please see the previous sections for more information about cases that these conditions are not met.


Apply Gradient Rotation to Column:

You can apply a rotational degree to the gradient of a column using the code below:

Columns(2).Interior.Gradient.Degree = 67

please note the code above only works if the following conditions exist:

  • A gradient already exists in column 2
  • The gradient of column 2 has the same properties (colors, rotations, …)

Please see the previous sections for more information about cases that these conditions are not met.


Apply Gradient Rotation to Row:

You can apply a rotational degree to the gradient of a row using the code below:

Rows(4).Interior.Gradient.Degree = 67

please note the code above only works if the following conditions exist:

  • A gradient already exists in row 4.
  • The gradient of row 4 has the same properties (colors, rotations, …)

Please see the previous sections for more information about cases that these conditions are not met.


Apply Gradient Rotation to Sheet:

You can apply a rotational degree to the gradient of a sheet using the code below:

Sheet2.Cells.Interior.Gradient.Degree = 67

please note the code above only works if the following conditions exist:

  • A gradient already exists in all of sheet 2.
  • The gradient of sheet 2 has the same properties (colors, rotations, …)

Please see the previous sections for more information about cases that these conditions are not met.
You can download the file and code related to this article here.

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 *