Modifying Chart Colors

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.

CROSS-REFERENCE  

Refer to Chapter 18 for more information about using VBA to work with charts .

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.

CD-ROM  

The examples in this section are available on the companion CD-ROM. The filename is  chart colors.xlsm .

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