format scatter chart legend

Sunil 1 Reputation point
2022-05-31T10:44:45.117+00:00

I have a VBA code for formatting marker of each series in scatter chart. When I want to show legend the same markers with coded colors are not seen. How do I show the legend matching with markers which are configured using VBA code. Code used is as follows.

Sub Macro1()
'
' Macro1 Macro
'

'
    Dim cht As Chart
    Dim ser As Series
    Dim pnt As Point

    Set cht = WS1.ChartObjects("Chart 1").Chart

'COLOR THE SERIES
    Set ser = ActiveChart.FullSeriesCollection(1)
    For Each pnt In ser.Points
    With pnt
        .MarkerStyle = 1
        .MarkerSize = 10
        .Format.Fill.Visible = msoTrue
        .MarkerBackgroundColor = RGB(252, 213, 181)
        .MarkerForegroundColor = RGB(0, 0, 0)
    End With
    Next
    ser.Format.Line.Visible = msoFalse
    Set ser = Nothing

'COLOR THE SERIES
        Set ser = ActiveChart.FullSeriesCollection(2)
    For Each pnt In ser.Points
    With pnt
        .MarkerStyle = 2
        .MarkerSize = 10
        .Format.Fill.Visible = msoTrue
        .MarkerBackgroundColor = RGB(51, 51, 51)
        .MarkerForegroundColor = RGB(0, 0, 0)
    End With
    Next
    ser.Format.Line.Visible = msoFalse
    Set ser = Nothing


'COLOR THE SERIES
        Set ser = ActiveChart.FullSeriesCollection(3)
    For Each pnt In ser.Points
    With pnt
        .MarkerStyle = 3
        .MarkerSize = 10
        .Format.Fill.Visible = msoTrue
        .MarkerBackgroundColor = RGB(255, 0, 0)
        .MarkerForegroundColor = RGB(0, 0, 0)
    End With
    Next
    ser.Format.Line.Visible = msoFalse
    Set ser = Nothing


'COLOR THE SERIES
    Set ser = ActiveChart.FullSeriesCollection(4)
    For Each pnt In ser.Points
    With pnt
        .MarkerStyle = 8
        .MarkerSize = 10
        .Format.Fill.Visible = msoTrue
        .MarkerBackgroundColor = RGB(0, 200, 50)
        .MarkerForegroundColor = RGB(0, 0, 0)
    End With
    Next
    ser.Format.Line.Visible = msoFalse
    Set ser = Nothing

'COLOR THE SERIES
    Set ser = ActiveChart.FullSeriesCollection(5)
    For Each pnt In ser.Points
    With pnt
        .MarkerStyle = 8
        .MarkerSize = 10
        .Format.Fill.Visible = msoTrue
        .MarkerBackgroundColor = RGB(0, 0, 255)
        .MarkerForegroundColor = RGB(0, 0, 0)
    End With
    Next
    ser.Format.Line.Visible = msoFalse

'COLOR THE SERIES
        Set ser = ActiveChart.FullSeriesCollection(6)
    For Each pnt In ser.Points
    With pnt
        .MarkerStyle = 9
        .MarkerSize = 10
        .Format.Fill.Visible = msoTrue
        .MarkerBackgroundColor = RGB(255, 255, 0)
        .MarkerForegroundColor = RGB(0, 0, 0)
    End With
    Next
    ser.Format.Line.Visible = msoFalse
    Set ser = Nothing


    cht.HasLegend = True
    cht.Legend.Position = xlLegendPositionRight
    cht.Legend.Format.Fill.Visible = msoFalse
    cht.Legend.Format.TextFrame2.TextRange.Font.Bold = msoTrue
    cht.Legend.Format.TextFrame2.TextRange.Font.Size = 16


End Sub
Microsoft 365 and Office | Development | Other
0 comments No comments
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.