Getting the wrong color for data points after I color dots in a xy scatterplot according to column value?

Question

I've worked on my code basis this question that was answered: How can I color dots in a xy scatterplot according to column value?

However, after I colour them by case, the colors that I have obtained are not entire accurate.

EDIT: I think I've found the problem. I have slicers to filter my scatter chart on Sheet 1, where the data is on Sheet 3.

E.g., the first 6 data points on my unfiltered data are "B / L / B / L / B", and the corresponding "Blue / Green / Blue / Green / Blue". However after I've applied for filters to only show for "L" which should only show Green, the first 6 data points are still showing "Blue / Green / Blue / Green / Blue". How should I go around this?

Sub ColorScatterPoints()

    Dim cht As Chart
    Dim srs As Series
    Dim srs_end As Long
    Dim pt As Point
    Dim p As Long
    Dim Vals$, lTrim#, rTrim#
    Dim valRange As Range, cl As Range
    Dim myColor As Long

    Set cht = ThisWorkbook.Worksheets(1).ChartObjects("Speed/Cons Chart").Chart
    Set srs = cht.SeriesCollection("with Full/Eco")

   '## Get the series Y-Values range address:
    lTrim = InStrRev(srs.Formula, ",", InStrRev(srs.Formula, ",") - 1, vbBinaryCompare) + 1
    rTrim = InStrRev(srs.Formula, ",")
    Vals = Mid(srs.Formula, lTrim, rTrim - lTrim)
    Set valRange = Range(Vals)  '#Column G / 7


    srs_end = ThisWorkbook.Worksheets(3).Cells(Rows.Count, 7).End(xlUp).Row - 1
    
    For p = 1 To srs_end
        Set pt = srs.Points(p)
        Set cl = valRange(p).Offset(0, -5) '## color is 5 columns to the left.

        With pt.Format.Fill
            .Visible = msoTrue
            Select Case cl
                Case "L"
                    myColor = RGB(112, 173, 71) 'should appear as Green
                Case "B"
                    myColor = RGB(68, 114, 196) 'should appear as Blue
            End Select

            .ForeColor.RGB = myColor

        End With
    Next

End Sub

After running the code, I noticed some of my data points labelled "L" are also in Blue. Vice-versa for my data points labelled "B".

Can someone show me where I got the code wrong?

Answer 1

If you have rows which could be filtered out then you need to loop the input range and not the series points:

Sub ColorScatterPoints()

    Dim cht As Chart
    Dim srs As Series
    Dim valRange As Range, c As Range, i As Long
    Dim myColor As Long

    Set cht = ThisWorkbook.Worksheets(1).ChartObjects("Speed/Cons Chart").Chart
    Set srs = cht.SeriesCollection("with Full/Eco")

   '## Get the series Y-Values range:
    Set valRange = Range(Split(srs.Formula, ",")(2))  '#Column G / 7

    'loop over the series Y-value range
    For Each c In valRange.Cells
        If Not c.EntireRow.Hidden Then 'check row is not filtered out
            i = i + 1                  'increment datapoint index
            Select Case c.Offset(0, -5).Value
                Case "L": myColor = RGB(112, 173, 71)
                Case "B": myColor = RGB(68, 114, 196)
                Case Else: myColor = RGB(150, 150, 150) '<< add a default
            End Select
            With srs.Points(i).Format.Fill 'format the i'th point
                .Visible = msoTrue
                .ForeColor.RGB = myColor
            End With
        End If
    Next c
    
End Sub