Chart Series Format Painter

[JGD]

Hello there,

I am using Excel 2010 and the format painted doesn't work for series within a chart. I was wondering if anyone out there has developed a macro to take its place?

Thanks,

Jon

[JGD]

This is what I ended up doing:

[VBA]
Dim MarkStyle, MarkSize, Fill As Integer
Dim ForeColour, BackColour, LineWeight, LineColour As Long

Sub GetRefData()


MarkStyle = Selection.MarkerStyle
MarkSize = Selection.MarkerSize
Fill = Selection.Format.Fill.Visible
ForeColour = Selection.MarkerForegroundColor
BackColour = Selection.MarkerBackgroundColor
LineColour = Selection.Format.Line.ForeColor
LineWeight = Selection.Format.Line.Weight
SelectSeries = Selection.Name
SelectChart = ActiveChart.Name
MsgBox (SelectChart & " " & SelectSeries & " formatting saved")
End Sub

Sub ApplyFormatting()
With Selection
.MarkerStyle = MarkStyle
.MarkerSize = MarkSize
.Format.Fill.Solid
.MarkerForegroundColor = ForeColour
.MarkerBackgroundColor = BackColour
.Format.Fill.Visible = Fill

End With

With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = LineColour
.Transparency = 0
.Weight = LineWeight
End With
End Sub

[/VBA]

It seems to work, with the little bit of testing that I have done with it so far. It doesn't get ALL of the formatting data, but only what I have to change everytime I make a new series in the many charts that I have. It coud be much more robust, but so far it does everything that I need it to do.

I had to assign the two macros to shortcut keys because everything is referenced to the currently selected series. It works, but it takes some explaining when I share the workbook with others.

Also, I had to use some global variables, which I'm a bit leary about. The rudimentary programming courses I have taken have always shunned global variables, and I was reluctant to use them, but I couldn't really think of a better way. Does anyone out there have a more elegant solution?