Excel VBA, Gradient, Rotation

Jul 26, 2015 by azurous in Excel

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:

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:

–

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.