conditionally format a line chart with markers.

B

Bernieg

Board Regular
Joined
Jan 1, 2009
Messages
147
Office Version
  1. 365
Platform
  1. Windows
Hi Guys

I'm trying to conditionally format a line chart with markers.
Using two columns Good/outliers & formatting the individual series works but loses the connecting lines between points.

I've tried to conditionally format using one column & series points, but with 1400 lines it takes 236 seconds.
Is there a faster way to do this.
takes about 10 seconds with 100 lines

Thanking you in advance
Bernie

VBA Code: 
Sub Format_Cht()
Application.ScreenUpdating = False
'Timer***************************************************
Dim secs1 As Single
Dim secs2 As Single
secs1 = Timer()
'********************************************************
Sheets("Sheet2").Select ' Data source sheet
Range("a1").Select

Dim t1 As Double
Dim t2 As Double
Dim ch As Chart
Dim S As Series
Dim I As Integer


t1 = Range("c2") ' UCL
t2 = Range("d2") ' LCL
Set ch = Worksheets("Sheet2").ChartObjects("Chart 1").Chart
Set S = ch.SeriesCollection("MR")
N = [count(a:a)]
I = 1

For I = 1 To N '
'Good Parts
If Not IsEmpty(S.Values(I)) And S.Values(I) >= t2 And S.Values(I) < t1 Then
S.Points(I).Select
' Connecting Line
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.RGB = RGB(0, 0, 0) ' black
    .Weight = 0.2
End With

'Marker
Selection.MarkerStyle = 8
Selection.MarkerSize = 5
  
With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 0, 255) 'Blue
End With

'Outliers
ElseIf Not IsEmpty(S.Values(I)) And S.Values(I) <= t2 Or S.Values(I) > t1 Then
S.Points(I).Select
' Connecting Line
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.RGB = RGB(255, 0, 0) ' red
    .Weight = 0.2
End With

'Marker
Selection.MarkerStyle = 1
Selection.MarkerSize = 5
  
With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
End With
End If
Next I
ActiveSheet.ChartObjects("Chart 1").Activate
Range("a1").Select

'Stop
' End of code to be timed
Range("F1") = "Format_Cht"
secs2 = Timer()
Worksheets("Sheet2").Range("G1").Value = secs2 - secs1
Application.ScreenUpdating = True

End Sub




Bernie
Excel Formula: 
[TABLE]
[TR]
[TD]IMRDATA[/TD]
[TD]MR[/TD]
[TD]UCL[/TD]
[TD]LCL[/TD]
[TD]MR[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][/TD]

[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.34[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.34[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.34[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.35[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.3[/RIGHT][/TD]
[TD][RIGHT]0.05[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.29[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.35[/RIGHT][/TD]
[TD][RIGHT]0.06[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.34[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.34[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.34[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.35[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.35[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD][RIGHT]0.04732[/RIGHT][/TD]
[TD]0[/TD]
[TD][RIGHT]0.01447[/RIGHT][/TD]
[/TR]
[TR]
[TD][RIGHT]10.34[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD][RIGHT]0.04732[/RIGHT][/TD]
[TD]0[/TD]
[TD][RIGHT]0.01447[/RIGHT][/TD]
[/TR]
[TR]
[TD][RIGHT]10.34[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.34[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.31[/RIGHT][/TD]
[TD][RIGHT]0.02[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0.02[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.31[/RIGHT][/TD]
[TD][RIGHT]0.02[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.34[/RIGHT][/TD]
[TD][RIGHT]0.02[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.34[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0.02[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.34[/RIGHT][/TD]
[TD][RIGHT]0.02[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.34[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.34[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.3[/RIGHT][/TD]
[TD][RIGHT]0.04[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.31[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.31[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.3[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0.03[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.28[/RIGHT][/TD]
[TD][RIGHT]0.05[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.3[/RIGHT][/TD]
[TD][RIGHT]0.02[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.3[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.3[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.3[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.31[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.34[/RIGHT][/TD]
[TD][RIGHT]0.02[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.3[/RIGHT][/TD]
[TD][RIGHT]0.04[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0.03[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.34[/RIGHT][/TD]
[TD][RIGHT]0.02[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.34[/RIGHT][/TD]
[TD][RIGHT]0.02[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.34[/RIGHT][/TD]
[TD][RIGHT]0.02[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0.02[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.25[/RIGHT][/TD]
[TD][RIGHT]0.08[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0.08[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.33[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.34[/RIGHT][/TD]
[TD][RIGHT]0.01[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.34[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.34[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[TR]
[TD][RIGHT]10.32[/RIGHT][/TD]
[TD][RIGHT]0.02[/RIGHT][/TD]
[TD]0.04732[/TD]
[TD]0.00000[/TD]
[TD]0.01447[/TD]
[/TR]
[/TABLE]
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Try this:

VBA Code: 
Sub Format_Cht()
  Dim secs1 As Single, secs2 As Single
  Dim t1 As Double, t2 As Double
  Dim ch As Chart
  Dim S As Series
  Dim i As Long, n As Long
  
  Application.ScreenUpdating = False
  'Timer***************************************************
    secs1 = Timer()
  '********************************************************
  
  Sheets("Sheet2").Select ' Data source sheet
  Range("a1").Select
  
  t1 = Range("c2") ' UCL
  t2 = Range("d2") ' LCL
  Set ch = Worksheets("Sheet2").ChartObjects("Chart 1").Chart
  Set S = ch.SeriesCollection("MR")
  n = [count(a:a)]
  i = 1
  
  For i = 1 To n '
    'Good Parts
    If Not IsEmpty(S.Values(i)) And S.Values(i) >= t2 And S.Values(i) < t1 Then
      ' Connecting Line
      With S.Points(i).Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 0, 0) ' black
        .Weight = 0.2
      End With
      
      'Marker
      S.Points(i).MarkerStyle = 8
      S.Points(i).MarkerSize = 5
      
      With S.Points(i).Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 0, 255) 'Blue
      End With
      
      'Outliers
    ElseIf Not IsEmpty(S.Values(i)) And S.Values(i) <= t2 Or S.Values(i) > t1 Then
      ' Connecting Line
      With S.Points(i).Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0) ' red
        .Weight = 0.2
      End With
      
      'Marker
      S.Points(i).MarkerStyle = 1
      S.Points(i).MarkerSize = 5
      
      With S.Points(i).Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
      End With
    End If
  Next i
  
  Range("F1") = "Format_Cht"
  secs2 = Timer()
  Worksheets("Sheet2").Range("G1").Value = secs2 - secs1
  Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution
DanteAmor said:
Try this:

VBA Code: 
Sub Format_Cht()
  Dim secs1 As Single, secs2 As Single
  Dim t1 As Double, t2 As Double
  Dim ch As Chart
  Dim S As Series
  Dim i As Long, n As Long
 
  Application.ScreenUpdating = False
  'Timer***************************************************
    secs1 = Timer()
  '********************************************************
 
  Sheets("Sheet2").Select ' Data source sheet
  Range("a1").Select
 
  t1 = Range("c2") ' UCL
  t2 = Range("d2") ' LCL
  Set ch = Worksheets("Sheet2").ChartObjects("Chart 1").Chart
  Set S = ch.SeriesCollection("MR")
  n = [count(a:a)]
  i = 1
 
  For i = 1 To n '
    'Good Parts
    If Not IsEmpty(S.Values(i)) And S.Values(i) >= t2 And S.Values(i) < t1 Then
      ' Connecting Line
      With S.Points(i).Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 0, 0) ' black
        .Weight = 0.2
      End With
     
      'Marker
      S.Points(i).MarkerStyle = 8
      S.Points(i).MarkerSize = 5
     
      With S.Points(i).Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 0, 255) 'Blue
      End With
     
      'Outliers
    ElseIf Not IsEmpty(S.Values(i)) And S.Values(i) <= t2 Or S.Values(i) > t1 Then
      ' Connecting Line
      With S.Points(i).Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0) ' red
        .Weight = 0.2
      End With
     
      'Marker
      S.Points(i).MarkerStyle = 1
      S.Points(i).MarkerSize = 5
     
      With S.Points(i).Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
      End With
    End If
  Next i
 
  Range("F1") = "Format_Cht"
  secs2 = Timer()
  Worksheets("Sheet2").Range("G1").Value = secs2 - secs1
  Application.ScreenUpdating = True
End Sub
Click to expand...
Thank you Dante Amour
That's worked a treat now takes 4.88 seconds to process 1436 rows
I see where i went wrong in selecting the the point.

Bernie
 
Upvote 0
DanteAmor said:
Try this:

VBA Code: 
Sub Format_Cht()
  Dim secs1 As Single, secs2 As Single
  Dim t1 As Double, t2 As Double
  Dim ch As Chart
  Dim S As Series
  Dim i As Long, n As Long
 
  Application.ScreenUpdating = False
  'Timer***************************************************
    secs1 = Timer()
  '********************************************************
 
  Sheets("Sheet2").Select ' Data source sheet
  Range("a1").Select
 
  t1 = Range("c2") ' UCL
  t2 = Range("d2") ' LCL
  Set ch = Worksheets("Sheet2").ChartObjects("Chart 1").Chart
  Set S = ch.SeriesCollection("MR")
  n = [count(a:a)]
  i = 1
 
  For i = 1 To n '
    'Good Parts
    If Not IsEmpty(S.Values(i)) And S.Values(i) >= t2 And S.Values(i) < t1 Then
      ' Connecting Line
      With S.Points(i).Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 0, 0) ' black
        .Weight = 0.2
      End With
     
      'Marker
      S.Points(i).MarkerStyle = 8
      S.Points(i).MarkerSize = 5
     
      With S.Points(i).Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 0, 255) 'Blue
      End With
     
      'Outliers
    ElseIf Not IsEmpty(S.Values(i)) And S.Values(i) <= t2 Or S.Values(i) > t1 Then
      ' Connecting Line
      With S.Points(i).Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0) ' red
        .Weight = 0.2
      End With
     
      'Marker
      S.Points(i).MarkerStyle = 1
      S.Points(i).MarkerSize = 5
     
      With S.Points(i).Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
      End With
    End If
  Next i
 
  Range("F1") = "Format_Cht"
  secs2 = Timer()
  Worksheets("Sheet2").Range("G1").Value = secs2 - secs1
  Application.ScreenUpdating = True
End Sub
Click to expand...
Hi Dante
Would it work even faster with an Array rather than Range.
Not sure how to do that

Bernie
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
How can I programatically format just the markers in a chart series, but leave the line alone?
Replies
6
Views
8K
jochoalions
J
S
  • Question Question
Format only chart series that currently has a line (ignore just markers)
Replies
1
Views
687
sndameron
S

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top