在根据列值对xy散点图中的点进行I着色后，得到错误的数据点颜色

Question

我已经在代码的基础上解决了这个问题：如何根据列值为xy散点图中的点着色？

然而，在我按案例给它们上色后，我得到的颜色并不完全准确。

编辑：我想我发现问题了。我有切片器来过滤第1张上的散点图，其中数据在第3张上。

例如，我的未过滤数据上的前6个数据点是"；B/L/B/L/B"；，并且相应的"；蓝色/绿色/蓝色/绿色-蓝色"；。然而，在我申请了过滤器之后；L"；前6个数据点仍然显示"绿色"；蓝色/绿色/蓝色/绿色-蓝色"；。我该如何绕过这个？

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

在运行代码之后，我注意到我的一些数据点标记为"；L"；也是蓝色的。反之亦然，对于标记为"0"的我的数据点；B"；。

有人能告诉我哪里的代码错了吗？

Answer 1

如果您有可以过滤掉的行，那么您需要循环输入范围，而不是序列点：

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