Excel VBA, Fill Effects, Gradient

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

Jump To:


Basics:

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

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

Excel Format Cell Dialog, Fill Tab
This article explains the different gradient effects applicable to cells using VBA for Exel:

Fill Effects Dialog


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:

Excel VBA, Create Gradient

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:

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


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:

Excel VBA, Gradient Rotation

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:

 

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 *