设置图表系列颜色以匹配类别单元格颜色VBA [英] Set Chart Series Colors to Match Category Cell Colors VBA

问题描述

我需要VBA宏，该宏将使我的类别背景颜色与折线图系列颜色匹配。
现在我没有使用最佳方法，因为我正在应用以下代码

I need VBA macro that will match my categories background colors with line chart series colors. Right now I'm not using best way as I'm applying following code

它设置图表系列颜色与源单元格颜色相同。 （图片上的示例）

It sets chart series colors same as source cell colors. (example on pic)

但我希望此宏从类别的单元格（代表2009、2010、2011）而不是源单元格获取颜色。

BUT I want this macro to take colors from categories's cells (2009, 2010, 2011 representatively) instead of source cells.

我找不到简单直接的方法。我正在宏设置源单元格的背景颜色以匹配类别颜色，然后使用条件格式将白色置于源单元格的顶部。因此，只有类别是彩色的，源单元是白色的。

I cannot find the way to do it simply and straight forward. I'm macro-setting background colors for source cells to match categories colors and then I'm putting white color on top of source cells with conditional formatting. So only categories are colorful and source cells are white.

想知道是否有更好的方法。 （图片的最终结果，类别名称与系列颜色匹配）

Was wondering if there's better way of doing that. (final result on pic, categories's names matching series colors)

Dim oChart As ChartObject
Dim MySeries As Series
Dim FormulaSplit As Variant
Dim SourceRange As Range
Dim SourceRangeColor As Long

'Loop through all charts in the active sheet
For Each oChart In ActiveSheet.ChartObjects

    'Loop through all series in the target chart
   For Each MySeries In oChart.Chart.SeriesCollection

        'Get Source Data Range for the target series
       FormulaSplit = Split(MySeries.Formula, ",")

        'Capture the first cell in the source range then trap the color
       Set SourceRange = Range(FormulaSplit(2)).Item(1)
        SourceRangeColor = SourceRange.Interior.Color

        On Error Resume Next
        'Coloring for Excel 2003
       MySeries.Interior.Color = SourceRangeColor
        MySeries.Border.Color = SourceRangeColor
        MySeries.MarkerBackgroundColorIndex = SourceRangeColor
        MySeries.MarkerForegroundColorIndex = SourceRangeColor

        'Coloring for Excel 2007 and 2010
       MySeries.MarkerBackgroundColor = SourceRangeColor
        MySeries.MarkerForegroundColor = SourceRangeColor
        MySeries.Format.Line.ForeColor.RGB = SourceRangeColor
        MySeries.Format.Line.BackColor.RGB = SourceRangeColor
        MySeries.Format.Fill.ForeColor.RGB = SourceRangeColor

    Next MySeries
Next oChart

End Sub

推荐答案

假设我完全了解您的要求，那么您已经很接近了。我认为您代码中的问题是您如何拆分系列公式以获取标签颜色。

Assuming that I understand exactly what you are asking, you were very close to being there. I think the issue in your code was how you were splitting the series formula to get the label color.

我打开了这张图表，并用以下标题绘制了列标题：

I turned this chart, with column headers colored as such:

使用以下代码进入下表：

into the chart below with this code:

Sub SetColors()

Dim oChart As ChartObject
Dim MySeries As Series

For Each oChart In ActiveSheet.ChartObjects

    For Each MySeries In oChart.Chart.SeriesCollection

        Dim sFormula As String
        sFormula = Split(MySeries.Formula, ",")(0) 'this returns the =SERIES(Sheet!RC part of the formula, the first argument is the series label
        sFormula = Split(sFormula, "(")(1) 'this removes the =SERIES(  leaving only the column label range (Sheet!RC)

        Dim lSourceColor As Long
        lSourceColor = Range(sFormula).Interior.Color

        With MySeries
            .Interior.Color = lSourceColor
            .Border.Color = lSourceColor
            '.MarkerBackgroundColorIndex = lSourceColor
            '.MarkerForegroundColorIndex = lSourceColor
            .MarkerBackgroundColor = lSourceColor
            .MarkerForegroundColor = lSourceColor
            With .Format.Line
                .ForeColor.RGB = lSourceColor
                .BackColor.RGB = lSourceColor
            End With
            .Format.Fill.ForeColor.RGB = lSourceColor
        End With

    Next

Next

End Sub

这篇关于设置图表系列颜色以匹配类别单元格颜色VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！