Modify marker style for select points 1

[Horstt]

I have an Excel add-on that graphs spc charts and colors "special cause points". Not all users have access to a color printer so I am trying to write a macro to change the marker Style and line style for these points which are colored red by the program. How do I identify the number of points in the graph? I do not know at design time what the source data worksheet name.

Dim x As Integer
On Error GoTo errorhandler

For x = 1 To _ActiveChart.SeriesCollection.PointsPoints.Count)

ActiveChart.SeriesCollection(8).Points(x).Select

If Selection.MarkerBackgroundColorIndex = 3 Then

With Selection.Border
.ColorIndex = 11
.Weight = xlMedium
.LineStyle = xlDot
End With

With Selection
.MarkerBackgroundColorIndex = 3
.MarkerForegroundColorIndex = 11
.MarkerStyle = xlCircle
.MarkerSize = 7
.Shadow = False
End With
End If

Next x

errorhandler: If Err.Number <> 0 Then
Dim a As Long
a = MsgBox(&quot;Error # &quot; & Err.Number & &quot; &quot; & Err.Description, vbOKOnly, &quot;Error&quot
End If

 

[RobBroekhuis]

The following code should work (it loops through all series - change if you just want one series treated).


Dim snr as integer, pnt As point

for snr=1 to activechart.seriescollection.count
for each pnt in ActiveChart.SeriesCollection(snr).Points
if pnt.MarkerBackgroundColorIndex = 3 Then
With pnt.Border
.ColorIndex = 11
.Weight = xlMedium
.LineStyle = xlDot
End With
pnt.MarkerBackgroundColorIndex = 3
pnt.MarkerForegroundColorIndex = 11
pnt.MarkerStyle = xlCircle
pnt.MarkerSize = 7
End If
Next pnt
next snr

Rob

 

[Horstt]

Thank you. It works perfectly.