Excel图表：如何让按行分类的数据标签着色适配任意行范围

问题背景 #

现有VBA代码仅支持固定第1行（分类）、第2行（数值）为图表数据标签按分类着色，需修改为支持任意两行范围（如第5/6行、27/28行），同时解决从图表系列公式中动态获取列索引colIndex的问题。

修改后的VBA代码 #

Sub Labels_SourceROWS_Dynamic()
    Dim p As Point
    Dim labelItems As Variant
    Dim length As Long
    Dim startPos As Long
    Dim color As Long
    Dim categoryColorRow As Long, valueColorRow As Long
    Dim sFormula As String, arrFormula As Variant
    Dim dataRange As Range
    Dim colIndex As Long
    
    ' 可自定义修改这里的行号，支持任意两行
    categoryColorRow = 5 ' 分类所在行
    valueColorRow = 6    ' 数值所在行
    
    With ActiveChart.SeriesCollection(1)
        .HasDataLabels = True
        With .DataLabels
            .ShowValue = True
            .ShowCategoryName = True
            .Separator = vbLf
            .Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = vbBlack
            .Format.TextFrame2.TextRange.Font.Bold = False
            .NumberFormat = "0.00"
            .Position = xlLabelPositionOutsideEnd
            .Font.Name = "Calibri"
            .Font.Size = 10
        End With
        
        ' 从系列公式中解析数据范围，获取起始列
        sFormula = .Formula
        sFormula = Mid(sFormula, InStr(sFormula, "(") + 1, InStr(sFormula, ")") - InStr(sFormula, "(") - 1)
        arrFormula = Split(sFormula, ",")
        Set dataRange = Range(arrFormula(2)) ' 系列数据所在范围
        colIndex = dataRange.Column ' 动态获取起始列索引
        
        For Each p In .Points
            labelItems = Split(p.DataLabel.Text, vbLf)
            labelItems(1) = Format(Replace(labelItems(1), ".", ","), "0.00")
            
            With p.DataLabel.Format.TextFrame2.TextRange
                .Text = labelItems(0) & vbLf & labelItems(1)
                startPos = 1
                
                ' 设置分类文本格式和颜色
                length = Len(labelItems(0))
                color = ActiveSheet.Cells(categoryColorRow, colIndex).Font.Color
                .Characters(startPos, length).Font.Bold = True
                .Characters(startPos, length).Font.Fill.ForeColor.RGB = color
                
                ' 设置数值文本格式和颜色
                startPos = startPos + length + 1
                length = Len(labelItems(1))
                color = ActiveSheet.Cells(valueColorRow, colIndex).Font.Color
                .Characters(startPos, length).Font.Bold = True
                .Characters(startPos, length).Font.Fill.ForeColor.RGB = color
            End With
            
            colIndex = colIndex + 1
        Next p
    End With
End Sub

关键改动说明 #

• 自定义行范围：直接修改代码开头的categoryColorRow和valueColorRow变量值，即可指定任意两行作为分类和数值行，无需修改核心逻辑。
• 动态获取列索引：通过解析图表系列的Formula属性，提取出数据所在的单元格范围，进而获取起始列colIndex，替代原来的硬编码值2，适配不同的数据列位置。
• 格式优化：调整了数值格式化逻辑，确保千分位和小数格式显示一致。

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