I wrote code to go through the data (as shown below) and collect data based on category. Such data will be added to dedicated data series and plotted at the ScatterPlot.

Series.Formula changes some characters e.g. commas, semicolons (pictures below) and an error pops up.
Dim mySeries As Series
For Each mySeries In ActiveSheet.ChartObjects("Test").Chart.SeriesCollection
For Each c In Source.Range("U7:U13")
If c = mySeries.Name Then
k = 0
Do While Cells(c.row, c.Column) = Cells(c.row + k, c.Column)
k = k + 1
Loop
k = k - 1
srsX = "{"
srsY = "{"
For j = c.row To c.row + k
srsX = srsX & (Cells(j, c.Column + 2)) & "\" '& Application.International(xlListSeparator)
srsY = srsY & (Cells(j, c.Column + 1)) & "\" '& Application.International(xlListSeparator)
Next j
srsX = Left(srsX, Len(srsX) - 1) & "}"
srsY = Left(srsY, Len(srsY) - 1) & "}"
With ActiveSheet.ChartObjects("Test").Chart.SeriesCollection
trashme = "=SERIES(""" & mySeries.Name & """" & Application.International(xlListSeparator) & srsX & Application.International(xlListSeparator) & srsY & Application.International(xlListSeparator) & mySeries.PlotColorIndex & ")"
MsgBox trashme
mySeries.Formula = "=SERIES(""" & mySeries.Name & """" & Application.International(xlListSeparator) & srsX & Application.International(xlListSeparator) & srsY & Application.International(xlListSeparator) & mySeries.PlotColorIndex & ")"
End With
End If
Next c
Next mySeries

