I am trying to write an Excel 2010 VBA subroutine to format charts according to a predefined (i.e., defined by me) standard. The particular attributes that I want to be able to set are merely the attributes that become available through the Format Data Series window that opens when one double-clicks on a data series.
In an effort to discover the names of the various properties that I would need to set, I recorded as a macro the changes that I was making to the style of the data series. However, although I can set the Line Color, and the Marker Line Color to different colours via the Format Data Series window, the recorded macro (annotated below) refers to identically named (indistinguishable) objects for the Line Color and the Marker Line Color.
In addition, when I actually run the macro, there are two problems. First, despite the fact that the recorded macro refers to the marker-fill property
.ForeColor.Brightness, that line produces an error when the macros is executed. The error says “Method Brightness of object ColorFormat failed". Second the recorded code actually simultaneously sets the marker line colour and the main line for the series, so in the recorded code, they are both first set to what I hoped would be the Marker Line Color, and then both set to what I wanted for the main Line Color.
How do I set the Marker Fill, Marker Line Colour, and Line Colour.
Sub Macro1() ' ' Macro6 Macro ' On Sheet 1 there is a single embedded chrt ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Select With Selection.Format.Fill .Visible = msoTrue .ForeColor.ObjectThemeColor = msoThemeColorText1 .ForeColor.TintAndShade = 0 ' The following (recorded line produces an error) .ForeColor.Brightness = 0.5 .Transparency = 0 .Solid End With With Selection.Format.Line .Visible = msoTrue .ForeColor.ObjectThemeColor = msoThemeColorAccent5 .ForeColor.TintAndShade = 0 .ForeColor.Brightness = 0.400000006 .Transparency = 0 End With With Selection.Format.Line .Visible = msoTrue .ForeColor.ObjectThemeColor = msoThemeColorAccent6 .ForeColor.TintAndShade = 0 .ForeColor.Brightness = -0.5 .Transparency = 0 End With End Sub