David -

Let me tweak your second macro:

Sub Macro2()
Dim srs As Series
For Each srs In ActiveChart.SeriesCollection
With srs
With .Border
.Weight = xlThin
.LineStyle = xlNone
End With
'.Shadow = False
'.InvertIfNegative = False
'.Interior.ColorIndex = xlAutomatic
End with
Next srs
End Sub

I've commented out the last three items inside With srs/End With because
they look redundant to me. Also, don't waste time selecting each series,
you can fully reference it without using the Selection object.

An alternative book for Excel VBA is "Excel 2002 VBA" (don't get "Excel
2003 VBA") by Bullen, Green, Bovey, and Rosenberg.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

David Howdon wrote:

> I'm trying to deal with the fairly well known problem of pivot charts
> resetting their formatting whenever the data is refreshed.
>
> I have a data series which I have graphed as a column chart (or vertical
> bar chart if you prefer), however because it has a lot of data points
> the columns are very thing and the colour of them cannot be seen because
> of their borders.
>
> Easy enough to fix, I simply remove the borders. So I recorded a macro
> when I did this which generated the following
>
> Sub Macro1()
> Attribute Macro1.VB_ProcData.VB_Invoke_Func = " \n14"
> ActiveChart.SeriesCollection(1).Select
> With Selection.Border
> .Weight = xlThin
> .LineStyle = xlNone
> End With
> Selection.Shadow = False
> Selection.InvertIfNegative = False
> Selection.Interior.ColorIndex = xlAutomatic
> End Sub
>
> However to make this work more generally on charts with multiple series
> I wanted a macro that would remove the borders for however many data
> series i had. So I wrote.
>
>
> Sub Macro2()
> Dim Item As Series
> For Each Item In SeriesCollection
> Item.Select
> With Selection.Border
> .Weight = xlThin
> .LineStyle = xlNone
> End With
> Selection.Shadow = False
> Selection.InvertIfNegative = False
> Selection.Interior.ColorIndex = xlAutomatic
> Next Item
> End Sub
>
> However this did not work giving RunTime Error 424
>
> Obviously I am missing something. Could anyone let me know how to
> achieve what I want. Also (and since I am trying to learn VB perhaps
> more importantly) could someone explain what I was doing wrong. Thanks.
>
> On a related note I'm currently learning VB using John Walkenbach's
> "Excel 2002 Power Programming with VBA". It seems quite usable so far
> but since it is always nice to have other options does anyone have
> suggestions for other good books on VBA for Excel 2002?
>
> Thanks again.
>
>