Hi, I have 38 years (columns) of temperature data, plotted by 2-week intervals (rows). I want to display 38 scatterplot charts--one for each year--let's call this the "study year". On each year's chart I wish to plot in grey all of the points for all of the years, except for the study year, which will be in red, in the foreground (not covered by other point). I have written a subroutine that will do this, except that the study year's points are not in the foreground--some are hidden behind other points (except for the 28th year's plot). Can anyone think of a way to have the points in the foreground? I can do this using the menus byt moving the study year's series to the bottom of the series list, but I don't know how to do this in VBA. The code I am using is below. Thank you!
Sub AddChartObject()
Dim j As Integer
Dim i As Integer
For j = 1 To 38
Dim myChtObj As ChartObject
Set myChtObj = Sheets("Charts1").ChartObjects.Add _
(Left:=j * 255, Width:=250, Top:=10, Height:=125)
myChtObj.Chart.SetSourceData Source:=Sheets("crosstabs weekly means by weekn").Range("e2:aq29")
myChtObj.Chart.ChartType = xlXYScatterLines
myChtObj.Chart.PlotBy = xlColumns
myChtObj.Chart.Legend.Font.Size = 4
myChtObj.Chart.Axes(xlValue).TickLabels.Font.Size = 6
myChtObj.Chart.Axes(xlCategory).TickLabels.Font.Size = 6
For i = 1 To 38
myChtObj.Chart.SeriesCollection(i).Select
Selection.MarkerStyle = 2
Selection.MarkerSize = 5
Selection.MarkerForegroundColor = RGB(128, 128, 128)
Selection.MarkerBackgroundColor = RGB(128, 128, 128)
Selection.Border.Color = RGB(128, 128, 128)
Selection.Format.Line.Visible = False
Next i
myChtObj.Chart.SeriesCollection(j).Select
Selection.MarkerStyle = 2
Selection.MarkerSize = 5
Selection.MarkerForegroundColor = RGB(255, 0, 0)
Selection.MarkerBackgroundColor = RGB(255, 0, 0)
Selection.Border.Color = RGB(255, 0, 0)
Selection.Format.Line.Visible = False
Next j
End Sub
Sub AddChartObject()
Dim j As Integer
Dim i As Integer
For j = 1 To 38
Dim myChtObj As ChartObject
Set myChtObj = Sheets("Charts1").ChartObjects.Add _
(Left:=j * 255, Width:=250, Top:=10, Height:=125)
myChtObj.Chart.SetSourceData Source:=Sheets("crosstabs weekly means by weekn").Range("e2:aq29")
myChtObj.Chart.ChartType = xlXYScatterLines
myChtObj.Chart.PlotBy = xlColumns
myChtObj.Chart.Legend.Font.Size = 4
myChtObj.Chart.Axes(xlValue).TickLabels.Font.Size = 6
myChtObj.Chart.Axes(xlCategory).TickLabels.Font.Size = 6
For i = 1 To 38
myChtObj.Chart.SeriesCollection(i).Select
Selection.MarkerStyle = 2
Selection.MarkerSize = 5
Selection.MarkerForegroundColor = RGB(128, 128, 128)
Selection.MarkerBackgroundColor = RGB(128, 128, 128)
Selection.Border.Color = RGB(128, 128, 128)
Selection.Format.Line.Visible = False
Next i
myChtObj.Chart.SeriesCollection(j).Select
Selection.MarkerStyle = 2
Selection.MarkerSize = 5
Selection.MarkerForegroundColor = RGB(255, 0, 0)
Selection.MarkerBackgroundColor = RGB(255, 0, 0)
Selection.Border.Color = RGB(255, 0, 0)
Selection.Format.Line.Visible = False
Next j
End Sub