Export (0) Print
Expand All

Using the Gradient Fill Features of the FillFormat Object in Excel 2010

Office 2010

Office Quick Note banner

Effectively Using Colors in Microsoft Excel 2010: Learn how to use the gradient features of the FillFormat object in Microsoft Excel 2010.

Last modified: May 12, 2011

Applies to: Excel 2010 | Office 2010 | VBA

In this article
Add the Code to the Visual Basic Editor
Test the Solution
Next Steps

Published:   May 2011

Provided by:    Frank Rice, Microsoft Corporation

The FillFormat object represents the fill formatting for a shape. A shape can have a solid, gradient, texture, pattern, picture, or semi-transparent fill. In this topic, you programmatically create two shapes and then add the gradient characteristics of the FillFormat object to those shapes. To complete this task, you must do the following:

In this task, you add programming code that demonstrates the gradient features of the FillFormat object.

To add code to the Visual Basic Editor

  1. Start Excel 2010.

  2. On the Developer tab, click Visual Basic to open the Visual Basic Editor.

    Note Note

    If you do not see the Developer tab in Excel 2010, click the File tab, and then click Options. In the categories pane, click Custom Ribbon, select Developer, and then click OK.

  3. In the Projects pane, click Sheet1.

  4. Paste or type the following Microsoft Visual Basic for Applications (VBA) code into the module window.

    Sub GradientDemo()
        Dim rng As Range
      
        Set rng = Range("B2:C10")
        rng.Cells.Merge
        Dim grd1 As LinearGradient
      
        rng.Interior.Pattern = XlPattern.xlPatternLinearGradient
        Set grd1 = rng.Interior.Gradient
      
        Dim cs As ColorStop
        ' Set the gradient to be tilted by 10 degrees:
        grd1.Degree = 10
      
        ' Add a color stop at 25% of the width:
        Set cs = grd1.ColorStops.Add(0.25)
        cs.Color = vbYellow
        ' Set the shading: -1 is black, 1 is white, 0 is the full color.
        ' Setting this to 0.25 makes it a little paler.
        cs.TintAndShade = 0.25
      
        ' Add a color stop at 100% of the width:
        Set cs = grd1.ColorStops.Add(1)
        cs.Color = vbRed
      
        ' Repeat with a rectangular gradient:
      
        Set rng = Range("E2:F10")
        Dim grd2 As RectangularGradient
        rng.Interior.Pattern = XlPattern.xlPatternRectangularGradient
        rng.Cells.Merge
      
        Set grd2 = rng.Interior.Gradient
        ' Set the offset for the rectangular gradient
        ' to the center of the region:
        grd2.RectangleLeft = 0.5
        grd2.RectangleTop = 0.5
      
        ' Add color stops at 25% and 100%:
        Set cs = grd2.ColorStops.Add(0.25)
        cs.Color = vbRed
      
        Set cs = grd2.ColorStops.Add(1)
        cs.Color = vbYellow
    End Sub
    
    
  5. Close the Visual Basic Editor.

In this task, you step through the VBA code line-by-line.

To run the code

  1. Drag the Visual Basic Editor window to the right side of your monitor.

  2. Drag the Excel window to the left side of your monitor and adjust the windows until you can see them both.

  3. Press F8 to step through the code line-by-line and watch the code interact with the worksheet.

Show:
© 2014 Microsoft