Sub Recolor()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rngSizes As Range
Dim rngColors As Range
Dim lngIndex As Long
Dim shp As Shape
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set shp1 = ws2.Shapes("Low")
Set shp2 = ws2.Shapes("Medium")
Set shp3 = ws2.Shapes("High")
' range containing marker size
Set rngSizes = ws1.Range("D2:D8")
Set rngColors = ws1.Range("E2:E8")
With ws1.ChartObjects(1).Chart
For lngIndex = 1 To .SeriesCollection.Count
With .SeriesCollection(lngIndex)
Select Case rngColors(lngIndex)
Case 1 To 9: 'Low case
shp1.Copy
.Points(1).Paste
With .Format.Line
.Visible = msoTrue
.Weight = 0.5
End With
With .Format.Fill 'no idea how to make the offsets
.OffsetLeft = 25
.OffsetRight = 25
.OffsetTop = 25
.OffsetBottom = 25
End With
With .Format.Glow
.Radius = 8
.Transparency = 0.599999994
.Color = RGB(0, 255, 0)
End With
Case 10 To 19: 'Medium case
shp2.Copy
.Points(1).Paste
With .Format.Line
.Visible = msoTrue
.Weight = 0.5
End With
With .Format.Fill 'no idea how to make the offsets
.OffsetLeft = 25
.OffsetRight = 25
.OffsetTop = 25
.OffsetBottom = 25
End With
With .Format.Glow
.Radius = 8
.Transparency = 0.599999994
.Color = RGB(255, 255, 0)
End With
Case Else: 'High case
shp3.Copy
.Points(1).Paste
With .Format.Line
.Visible = msoTrue
.Weight = 0.5
End With
With .Format.Fill 'no idea how to make the offsets
.OffsetLeft = 25
.OffsetRight = 25
.OffsetTop = 25
.OffsetBottom = 25
End With
With .Format.Glow
.Radius = 8
.Transparency = 0.599999994
.Color = RGB(255, 0, 0)
End With
End Select
End With
Next
End With
End Sub