Changing point markers, size and colour on scatter chart

Question

I am trying to create a scatter chart where the point colour, marker size and marker style for each plot point are set based on parameters in my data table.

My table has about 50 rows of data, across six columns A to F. Column A = Name, B = X coordinate, C = Y coordinate, D = Marker size, E = Marker style, and F = Marker colour. I am trying to plot a scatter chart which has each point looking different from the others in shape, size and colour, depending on the value in their respective column for that row in the table.

I have built the VBA to create a scatter chart, and it works fine and I get the point labels working correctly, which shows me I am getting the right info pulling from the table per point. However, when I try and change the size, shape and colour parameters, ALL of the points change (i.e. they all turn into purple size 5 triangles), rather than each one being driven by its own parameters.

Stumped here. Any help or suggestions much appreciated.

Axuary Axuary · Accepted Answer · 2021-01-20T00:54:31

I am not sure what the particulars are in you case but I got the following code to work on the spreadsheet below.

Sub TestScatter()
    Dim p As Point
    Dim i As Long

    ActiveSheet.ChartObjects("Chart 1").Activate
    i = 1
    For Each p In ActiveChart.SeriesCollection(1).Points
        i = i + 1
        p.MarkerSize = Cells(i, 4)
        Select Case Cells(i, 5)
            Case "Circle"
                p.MarkerStyle = xlMarkerStyleCircle
            Case "Square"
                p.MarkerStyle = xlMarkerStyleSquare
            Case "Triangle"
                p.MarkerStyle = xlMarkerStyleTriangle
            Case Else
                p.MarkerStyle = xlmarkerstyledefault
        End Select
        Select Case Cells(i, 6)
            Case "Red"
                p.MarkerBackgroundColor = RGB(255, 0, 0)
                p.MarkerForegroundColor = RGB(255, 0, 0)
            Case "Green"
                p.MarkerBackgroundColor = RGB(0, 255, 0)
                p.MarkerForegroundColor = RGB(0, 255, 0)
            Case "Blue"
                p.MarkerBackgroundColor = RGB(0, 0, 255)
                p.MarkerForegroundColor = RGB(0, 0, 255)
            Case Else
                p.MarkerBackgroundColor = RGB(0, 0, 0)
                p.MarkerForegroundColor = RGB(0, 0, 0)
        End Select
    Next p
End Sub

Changing point markers, size and colour on scatter chart

1 Answers