This article explain how you can work with cell fill effects (Gradient) in VBA for excel.

Jump To:

• Basics
• Create gradient
• Gradient Colors
• Fill Effects Shading Style, Rotational Degree

Basics:

Fill effects (Gradients) can be changed manually by right clicking on a cell and pressing format cells:

After clicking on format cells, choose the fill tab from the dialog that opens and press the fill effects button:

This article explains the different gradient effects applicable to cells using VBA for Exel:

Create gradient:

In order to be able to apply the codes in this article you must first create a gradient. The code below creates a gradient for the cell A1:

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


The code above creates the default gradient style:

For code snippets regarding creating gradients for:

• Ranges
• Columns
• Rows
• Sheets

Please see the article Excel, VBA, Create Gradient.

Gradient Colors:

The fill effects dialog for gradients has 2 colors:

Unlike the manual dialog, when working with gradient in VBA, we can have as many colors as we need. The article Excel VBA, Gradient’s Colors explains the basics of working with gradient colors. Also the articles below provide samples codes for modifying gradient colors. Though they assume you are familiar with the first article mentioned:

• Excel VBA Range Gradient Color
• Excel VBA, Column Gradient Color
• Excel VBA, Row Gradient Color
• Excel VBA, Sheet Gradient Color

Fill Effects Shading Style, Rotational Degree:

You can manually change the rotational degree of the gradient style using the radio buttons bellow. The dialog provides limited options for modifying the rotational degree of gradients:

The rotation of the gradient of a cell can be changed using the code below:

Range("A1").Interior.Gradient.Degree = 45


However if the code above is not used properly could cause a runtime error. For more information on modifying the rotational degree of gradients topic please see Excel VBA, Gradient, Rotation. The article also explains how you can change the rotation of the gradient of a:

• Range of cells
• Column
• Row
• Sheet

See also:

• Excel, VBA, Create Gradient
• Excel VBA, Gradient’s Colors
• Excel VBA, Gradient, Rotation

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