Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

This section describes how to change colors in a chart. The most important point is to identify the specific chart element that you want to modify. In other words, you need to identify the object and then set the appropriate properties.

Figure 30-14 shows a simple column chart named Chart 1. This chart has two data series, a legend, and a chart title.

Figure 30-14: A simple column chart.

Following is a VBA statement that changes the color of the first data series to red.

ActiveSheet.ChartObjects("Chart 1").Chart. _ SeriesCollection(1).Format.Fill.ForeColor.RGB = vbRed

To the uninitiated, this statement is probably confusing because so many objects are involved. The object hierarchy is as follows :

The active sheet contains a ChartObjects collection. One object in that collection is the ChartObject named Chart 1. The Chart property of the ChartObject object returns a Chart object. The Chart object has a SeriesCollection collection, and one Series object in the collection has an index number of 1. The Format property of the Series object returns a ChartFormat object. The Fill property of the ChartFormat object returns a FillFormat object. The ForeColor property of the FillFormat object returns a ColorFormat object. The RGB property of the ColorFormat object is set to red.

Another way of writing the preceding statement, using object variables to identify the individual objects (and, perhaps, clarify the objects' relationships), is

Sub ChangeSeries1Color Dim MyChartObject As ChartObject Dim MyChart As Chart Dim MySeries As Series Dim MyChartFormat As ChartFormat Dim MyFillFormat As FillFormat Dim MyColorFormat As ColorFormat ' Create the objects Set MyChartObject = ActiveSheet.ChartObjects("Chart 1") Set MyChart = MyChartObject.Chart Set MySeries = MyChart.SeriesCollection(1) Set MyChartFormat = MySeries.Format Set MyFillFormat = MyChartFormat.Fill Set MyColorFormat = MyFillFormat.ForeColor ' Change the color MyColorFormat.RGB = vbRed End Sub

The RGB property accepts a decimal color value, which I specified using a built-in VBA constant. Other color- related properties of the ColorFormat object are

• ObjectThemeColor : A number between 0 and 16 that represents the theme color. VBA provides constants for these values. For example, msoThemeColorAccent3 contains the value 7.

• TintAndShade : A number between “1 and +1 that represents the tint or shade of the theme color.

You can also specify color gradients. Here's an example that applies a preset gradient to the second data series in a chart. Notice that the gradient is set using the FillFormat object:

Sub AddPresetGradient() Dim MyChart As Chart Set MyChart = ActiveSheet.ChartObjects("Chart 1").Chart With MyChart.SeriesCollection(1).Format.Fill .PresetGradient _ Style:=msoGradientHorizontal, _ Variant:=1, _ PresetGradientType:=msoGradientFire End With End Sub

Working with other chart elements is similar. The procedure that follows changes the colors of the chart's chart area and plot area, using colors from the current document theme:

Sub RecolorChartAndPlotArea() Dim MyChart As Chart Set MyChart = ActiveSheet.ChartObjects("Chart 1").Chart With MyChart .ChartArea.Format.Fill.ForeColor.ObjectThemeColor = _ msoThemeColorAccent6 .ChartArea.Format.Fill.ForeColor.TintAndShade = 0.9 .PlotArea.Format.Fill.ForeColor.ObjectThemeColor = _ msoThemeColorAccent6 .PlotArea.Format.Fill.ForeColor.TintAndShade = 0.5 End With End Sub

The final example in this section applies a random color to each chart element. Using this macro virtually guarantees an ugly chart. However, this code demonstrates how to change the color for other chart elements. The UseRandomColors procedure uses a simple function, RandomColor , to determine the color used.

Sub UseRandomColors() Dim MyChart As Chart Set MyChart = ActiveSheet.ChartObjects("Chart 4").Chart With MyChart .ChartArea.Format.Fill.ForeColor.RGB = RandomColor .PlotArea.Format.Fill.ForeColor.RGB = RandomColor .SeriesCollection(1).Format.Fill.ForeColor.RGB = RandomColor .SeriesCollection(2).Format.Fill.ForeColor.RGB = RandomColor .Legend.Font.Color = RandomColor .ChartTitle.Font.Color = RandomColor .Axes(xlValue).MajorGridlines.Border.Color = RandomColor .Axes(xlValue).TickLabels.Font.Color = RandomColor .Axes(xlValue).Border.Color = RandomColor .Axes(xlCategory).TickLabels.Font.Color = RandomColor .Axes(xlCategory).Border.Color = RandomColor End With End Sub Function RandomColor() RandomColor = Application.RandBetween(0, RGB(255, 255, 255)) End Function