Changing an Object’s Fill in Excel 2007:-


As shown in the Figure (given below) the Shape Fill drop-down on the Format ribbon allows you to choose a single color, a gradient, a picture, or a texture for the fill.



(Fill options include a solid color, a gradient, a texture, or a picture.)

To apply a specific color, you can use the RGB (red, green, blue) setting. To create a color, you specify a value from 0 to 255 for levels of red, green, and blue. The following code applies a simple blue fill:

Dim cht As Chart

Dim upb As UpBars

Set cht = ActiveChart

Set upb = cht.ChartGroups(1).UpBars

upb.Format.Fill.ForeColor.RGB = RGB(0, 0, 255)

If you would like an object to pick up the color from a specific theme accent color, you use the ObjectThemeColor property. The following code changes the bar color of the first series to accent color 6 (which is an orange color in the Office theme but might be another color if the workbook is using a different theme):

Sub ApplyThemeColor()

Dim cht As Chart

Dim ser As Series

Set cht = ActiveChart

Set ser = cht.SeriesCollection(1)

ser.Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent6

End Sub

To apply a built-in texture, you use the PresetTextured method. The following code applies a green marble texture to the second series, but there are 20 different textures that can be applied:

Sub ApplyTexture()

Dim cht As Chart

Dim ser As Series

Set cht = ActiveChart

Set ser = cht.SeriesCollection(2)

ser.Format.Fill.PresetTextured (msoTextureGreenMarble)

End Sub

To fill the bars of a data series with a picture, you use the UserPicture method and specify the path and filename of an image on the computer, as in the following example:

Sub FormatWithPicture()

Dim cht As Chart

Dim ser As Series

Set cht = ActiveChart

Set ser = cht.SeriesCollection(1)

MyPic = “C:\PodCastTitle1.jpg”

ser.Format.Fill.UserPicture (MyPic)

End Sub

Gradients are more difficult to specify than fills. Excel 2007 offers three methods that help you set up the common gradients. The OneColorGradient and TwoColorGradient methods require that you specify a gradient direction such as msoGradientFromCorner. You can then specify one of four styles, numbered 1 through 4, depending on whether you want the gradient to start at the top left, top right, bottom left, or bottom right. After using a gradient method, you need to specify the ForeColor and the BackColor settings for the object. The following macro sets up a two-color gradient using two theme colors:

Sub TwoColorGradient()

Dim cht As Chart

Dim ser As Series

Set cht = ActiveChart

Set ser = cht.SeriesCollection(1)

MyPic = “C:\PodCastTitle1.jpg”

ser.Format.Fill.TwoColorGradient msoGradientFromCorner, 3

ser.Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent6

ser.Format.Fill.BackColor.ObjectThemeColor = msoThemeColorAccent2

End Sub

When using the OneColorGradient method, you specify a direction, a style (1 through 4), and a darkness value between 0 and 1 (0 for darker gradients or 1 for lighter gradients).

When using the PresetGradient method, you specify a direction, a style (1 through 4), and the type of gradient (for example, msoGradientBrass, msoGradientLateSunset, or msoGradientRainbow). Again, as you are typing this code in the VBA editor, the AutoComplete tool provides a complete list of the available preset gradient types.