Adding columns of data to series in scatter plot

Question

I created a VBA to generate a scatter plot based on a row of data if the date in column A is highlighted. The scatter plot generates but the series pulls the two highlighted dates and then it pulls in all of the data from subsequent columns. I only want the series to pull the highlighted dates from Column A.

This is my VBA:

Dim X As Long, LastRow As Long, J As Long, Cell As Range
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(ActiveSheet.Name)

Const StartRow As Long = 3
Const DataCol As String = "A"
LastRow = ws.Cells(Rows.Count, DataCol).End(xlUp).Row

J = 1

Dim Chart1 As Chart
Set Chart1 = Charts.Add

With Chart1
    .ChartType = xlXYScatterLines
    
For X = StartRow To LastRow
On Error Resume Next
If ws.Cells(X, DataCol).Interior.Color = 65535 Then


    .SeriesCollection.NewSeries
    .SeriesCollection(J).Name = ws.Cells(X, DataCol).Value
    .SeriesCollection(J).XValues = ws.Range("$B$2:$AA$2")
    .SeriesCollection(J).Values = ws.Range(ws.Cells(X, 2), ws.Cells(X, 27))

J = J + 1
Else
J = J
 End If
 Next X
 End With

End Sub

Answer 1

Charts.Add will attempt to 'guess' the source data based on the current region surrounding the active cell.

So you can either set the active cell to some blank cell in an unused range prior to running or, perhaps more sensibly, add the line

Chart1.ChartArea.Clear

immediately after

Set Chart1 = Charts.Add