Hi,

I have a VBA module that colours the markers of an existing scatter plot (code below). I would like to add another data series to the chart. This will be the X,Y points of a circle. The X,Y values are in TestSheet in range V2:W362. They will use the same X,Y axes as the existing chart. I would like the circle to be a smooth curve in red, thickness 1, no markers. I think this can be achieved by adding a few lines of code in the module shown.

Can anybody help?

Thanks,


Option Explicit

Function normalizeLookUp(datum As Variant, dataMin As Double, dataMax As Double, n As Integer) As Integer
    normalizeLookUp = CInt(((datum - dataMin) / (dataMax - dataMin)) * (n - 1)) + 1
End Function

Sub colourChartLookUp()

    Dim data As Variant
    Dim dataMin As Double
    Dim dataMax As Double

    Dim lastRow As Integer
    Dim ws As Worksheet
    
    Dim datum As Integer
    Set ws = Worksheets("TestSheet")
    
    lastRow = ws.Range("J1").End(xlDown).row
    data = ws.Range("J1:J" & lastRow)
    dataMin = Application.Min(data)
    'to clip to 30 comment out next line and use dataMax = 30...I think!
    'dataMax = Application.Max(data)
    dataMax = 30
    
    Dim n As Integer
    n = ws.Range("A1").End(xlDown).row

    With ws.ChartObjects("Chart 1").Chart.SeriesCollection(1)

        Dim Count As Integer
        Dim colourRow As Integer
        For Count = 1 To UBound(data)
             datum = data(Count, 1)
                colourRow = normalizeLookUp(datum, dataMin, dataMax, n)
                .Points(Count).Format.Fill.BackColor.RGB = RGB(ws.Range("C" & colourRow).Value, ws.Range("D" & colourRow).Value, ws.Range("E" & colourRow).Value)
        Next Count

    End With

End Sub