JJ,

You can use macros to automate a lot of the series changes you want . . .

The first macro below will return the series color index for series number
1. If you dont know the color index for the series that you want, try a
color and then run this. You can then apply the number to the second and
third macros below.

If you want the marker colors, take out the apostrophe in front of those
lines of code:

Sub SeriesColorIndex()
€˜get color indices
Dim A As Integer 'Line Color Index
Dim B As Integer 'Marker Background Color Index
Dim C As Integer 'Marker Foreground Color Index
Set Cht = ActiveChart
Set Srs1 = Cht.SeriesCollection(1)
A = Srs1.Border.ColorIndex
€˜B = Srs1.MarkerBackgroundColorIndex
€˜C = Srs1.MarkerForegroundColorIndex
MsgBox A
MsgBox B
MsgBox C
End Sub

This macro colors all of the series the same color:

Sub ColorAllSeries()
Set Cht = ActiveChart
Set Srs = Cht.SeriesCollection(2)
For Each Sr In Srs
Sr.Border.ColorIndex = 6
€˜Sr.MarkerBackgroundColorIndex = 6
€˜Sr.MarkerForegroundColorIndex = 6
Next Sr
End Sub

This macro colors the series number 2:

Sub ColorSingleSeries()
Set Cht = ActiveChart
Set Srs = Cht.SeriesCollection(2)
Srs.Border.ColorIndex = 6
€˜Srs.MarkerBackgroundColorIndex = 6
€˜Srs.MarkerForegroundColorIndex = 6
End Sub

To create a custom chart that you base all of your other on (so you dont
have to format each line in each new chart that you create), click on your
master chart and go through the following:

Chart - Chart Type - Custom Types Tab
Go to Select From - User Defined
Hit €œAdd€�
Give the chart a name and description
Save the chart and use it as the default for your others

----
Regards,
John Mansfield
http://www.pdbook.com


"jj" wrote:

I am trying to change the datasource of an existing chart
in VBA. Is there a way to do this in VBA. Our isssue is that we are moving to
another source.

So the example would be we take a range from an existing worksheet and move
this to a new worksheet with the chart, but not in the same range. So the
variables would be that we need to adjust the source range be the difference
in the row or columns for each of the series. The main issue is how do we get
the data source of the existing chart series?

Thanks for any help.