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
Bookmarks