问题背景 #

我尝试依据数据标签为图表标记设置颜色，使用Point.Format.Fill.ForeColor.RGB属性，每个点的颜色取自与数据标签文本匹配的单元格，核心实现代码如下：

Function doOnePoint(aPt As Point, DataLabel As String, Rng_colours As Range)
    Dim Rslt As Long
    On Error GoTo ErrXIT
    Rslt = Application.WorksheetFunction.Match(DataLabel, Rng_colours, 0)
    aPt.Format.Fill.ForeColor.RGB = Rng_colours.Cells(Rslt).Interior.Color
    aPt.Format.Line.Transparency = 1#
ErrXIT:
End Function

异常现象 #

• 代码可成功修改图表标记颜色，但对数据源表格首次排序时，图表所有标记颜色会发生变更；首次排序后再执行其他排序操作，颜色则能保持不变。按预期，颜色应与数据点绑定，不受排序影响。
• 执行颜色赋值操作后，aPt.Format.Fill.ForeColor.RGB的返回值为0，但图表标记颜色实际已成功修改。

图表对比 #

• 初始（预期）图表：
• 按Y值排序后的图表：

示例表格 #

完整着色脚本 #

Option Explicit

Function doOnePoint(aPt As Point, DataLabel As String, Rng_colours As Range)
    Dim Rslt As Long
    On Error GoTo ErrXIT
    Rslt = Application.WorksheetFunction.Match(DataLabel, Rng_colours, 0)
    aPt.Format.Fill.ForeColor.RGB = Rng_colours.Cells(Rslt).Interior.Color
    aPt.Format.Line.Transparency = 1#
ErrXIT:
End Function

Function doOneSeries(aSeries As Series, Rng_colours As Range)
    Dim I As Long
    For I = 1 To aSeries.Points.Count
        doOnePoint aSeries.Points(I), aSeries.DataLabels(I).Text, Rng_colours
    Next I
End Function

Sub doChartConditionalColor()
    If ActiveChart Is Nothing Then _
        MsgBox "请先选择一个图表再运行此程序": GoTo ErrXIT
        
    Dim Rng_colours As Range
    Set Rng_colours = Application.InputBox( _
        "请选择包含条件颜色的单元格区域", Type:=8)
        
    If Rng_colours Is Nothing Then _
        MsgBox "无效区域": GoTo ErrXIT
    
    If TypeOf Selection Is Series Then
        doOneSeries Selection, Rng_colours
    Else
        Dim aSeries As Series
        For Each aSeries In ActiveChart.SeriesCollection
            doOneSeries aSeries, Rng_colours
        Next aSeries
    End If
ErrXIT:
End Sub

内容的提问来源于stack exchange，提问作者Inn0cent