How to Keep Marker Borders But Remove Connecting Lines in a Scatter Plot?

Hello Excel VBA Experts,

I'm facing a frustrating issue when creating a scatter plot in Excel 2019 using VBA. My goal is: ✅ Each point should have a fill color (Column C, in RGB format like RGB(255,0,0)). ✅ Each point should have an edge (border) color (Column E, in RGB format like RGB(0,0,255)). ✅ NO connecting lines should appear between points.

The problem: Every time I set an edge color, Excel automatically adds a connecting line between the points, even though I try to disable it. If I set .Format.Line.Visible = msoFalse, it removes both the connecting line and the marker border. ❌ If I set .Format.Line.ForeColor.RGB = RGB(x, y, z), the connecting lines appear in that color. ❌ Manually, I can go to Format Data Series → Line → "No Line", and it works! But I need a VBA solution. Below is the simplified version of the code:

Sub CreateScatterPlot()
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim scatterChart As Chart
    Dim xValues As Range, yValues As Range, fillColors As Range, edgeColors As Range
    Dim i As Integer
    Dim series As Series
    Dim pt As Point

    ' Set worksheet and data ranges
    Set ws = ActiveSheet
    Set xValues = ws.Range("B2:B" & ws.Cells(Rows.Count, "B").End(xlUp).Row)
    Set yValues = ws.Range("A2:A" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
    Set fillColors = ws.Range("C2:C" & ws.Cells(Rows.Count, "C").End(xlUp).Row)
    Set edgeColors = ws.Range("E2:E" & ws.Cells(Rows.Count, "E").End(xlUp).Row)

    ' Create scatter plot
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=500, Top:=50, Height:=400)
    Set scatterChart = chartObj.Chart
    scatterChart.ChartType = xlXYScatter
    scatterChart.HasLegend = False

    ' Add data series
    Set series = scatterChart.SeriesCollection.NewSeries
    With series
        .XValues = xValues
        .Values = yValues
        .MarkerStyle = xlMarkerStyleCircle
        .MarkerSize = 11.34
    End With

    ' Apply colors point by point
    For i = 1 To xValues.Rows.Count
        Set pt = series.Points(i)
        pt.Format.Fill.ForeColor.RGB = TextRGBToRGB(fillColors.Cells(i, 1).Value)
        pt.Format.Line.ForeColor.RGB = TextRGBToRGB(edgeColors.Cells(i, 1).Value)
        pt.Format.Line.Weight = 1.5
    Next i

    ' Attempt to remove connecting lines (this doesn't work)
    series.Format.Line.Visible = msoFalse
    End Sub

    ' Converts "RGB(r,g,b)" text format to actual RGB function
    Function TextRGBToRGB(rgbText As String) As Long
    Dim r As Integer, g As Integer, b As Integer
    Dim rgbValues As Variant
    rgbText = Replace(Replace(rgbText, "RGB(", ""), ")", "")
    rgbValues = Split(rgbText, ",")
    If UBound(rgbValues) = 2 Then
        r = Val(rgbValues(0))
        g = Val(rgbValues(1))
        b = Val(rgbValues(2))
        TextRGBToRGB = RGB(r, g, b)
    Else
        TextRGBToRGB = RGB(0, 0, 0)
    End If
End Function

What I've Tried (None Worked) ✔ .Format.Line.Visible = msoFalse (removes both edge color and line). ✔ .Format.Line.ForeColor.RGB = RGB(r,g,b) (adds a connecting line). ✔ .Format.Line.Transparency = 1 (no effect). ✔ .MarkerBorder = True (not a valid property). ✔ Manually setting "No Line" in "Format Data Series" works, but I need a VBA solution.`

Has anyone found a working VBA method for this issue? Is this a bug in Excel 2019, or am I missing something? Thanks in advance! 🙏 Here is a link for an Excel Sheet with essential data: Sample Excel Data

Solution

Instead of

.Format.Fill.ForeColor.RGB
.Format.Line.ForeColor.RGB

You can use:

.MarkerBackgroundColor = RGB(255, 0, 0)
.MarkerForegroundColor = RGB(0, 0, 255)

Demo code:

Sub CreateScatterPlot()

    ' Set worksheet and data ranges
    Dim ws As Worksheet
    Dim xValues As Range, yValues As Range
    Dim fillColors As Range, edgeColors As Range
    Dim chartObj As ChartObject, scatterChart As Chart
    Dim series As series
    Dim i As Long

    Set ws = ActiveSheet
    Set xValues = ws.Range("B2:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row)
    Set yValues = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
    'Set fillColors = ws.Range("C2:C" & ws.Cells(ws.Rows.Count, "C").End(xlUp).Row)
    'Set edgeColors = ws.Range("E2:E" & ws.Cells(ws.Rows.Count, "E").End(xlUp).Row)

    ' Create scatter plot
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=500, Top:=50, Height:=400)
    Set scatterChart = chartObj.Chart
    scatterChart.ChartType = xlXYScatter
    scatterChart.HasLegend = False

    ' Add data series
    Set series = scatterChart.SeriesCollection.NewSeries
    With series
        .xValues = xValues
        .Values = yValues
        .MarkerStyle = xlMarkerStyleCircle
        .MarkerSize = 20
    End With

    ' Apply colors point by point
    With scatterChart.FullSeriesCollection(1)
        For i = 1 To xValues.Rows.Count
            .Points(i).MarkerBackgroundColor = RGB(255, 0, 0)
            .Points(i).MarkerForegroundColor = RGB(0, 0, 255)
        Next i
    End With

End Sub