Help Custom Color ScatterPlot Points with VBA

[largeselection]

Hi,

I'm trying to color some datapoints in a scatterplot based on the color in the neighboring cell where the data is. I've written some code, but I'm not sure why it's not working. I don't get any errors, but the datapoints do not change color. Basically I recorded myself selecting a datapoint and then altered what was recorded to apply to all the points.

Range("F28").Select
Dim rng As Range
Set rng = Range("M5:M15")
Do Until IsEmpty(ActiveCell)
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Points(Range(Range("F28"), ActiveCell).Rows.Count).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = Range("M4").Offset(Application.Match(ActiveCell.Offset(0, 1).Value, rng, 0), 0).Interior.Color
        .Transparency = 0.5
    End With
   
ActiveCell.Offset(1, 0).Select

The reason why I have the Points(Range(Range... part is because the first row of data corresponds to Points(1) as I found out by recording. The second row of data corresponds to Points(2), etc. So since I am starting at the first row of data I want the Point to be edited to be the one equal to the count of rows until that row.

Any help is much appreciated

 

[largeselection]

I tried a different approach and now I'm seeing weird stuff. So I tried a different bit of code and if I step through I can clearly see it changing the color of the datapoint, but as soon as it gets to the "END WITH" part the color immediately reverts back to the default.

Any ideas how to get it to stick?

Dim rng As Range
Set rng = Range("M5:M15")
Dim i, ptcnt As Integer
Dim Cht As Chart
Set Cht = ActiveSheet.ChartObjects(1).Chart
ptcnt = Cht.SeriesCollection(1).Points.Count
For i = 1 To ptcnt
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Points(i).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = Range("M4").Offset(Application.Match(Cells(i + 27, 7).Value, rng, 0), 0).Interior.Color
        .Solid
    End With
Next i

 

[largeselection]

Still don't have an answer, but I found another clue!

It must have sometime to do with the data series options. So if I go to format data series and marker fill I first saw that "vary colors by point" was deselected. So I tried selecting it and the points changed colors, but not to the colors I wanted.

Then I saw it said "automatic" so I thought maybe that was it. But even if I choose "solid fill" with vary colors by point, then run my code, it still does not give me the correct colors in the points.

Should I be doing something different than the fill? or any ideas how to get it to stick?