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
- Example 2, Preserve Colors
- Apply Gradient Rotation to Range
- Apply Gradient Rotation to Column
- Apply Gradient Rotation to Row
- Apply Gradient Rotation to Sheet
Contents
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:
In the example below the user selects 135 degrees:
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:
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:
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