需要使用VBA更改数据透视图上的颜色 [英] Need to change colors on the Pivot Chart using VBA

问题描述

问题:

我正在使用Excel 2010和多个数据透视图生成报告.生成报告时，无法将数据透视图系列的颜色设置为静态值.有时通过"系列显示为红色"，这会造成混乱.

I am generating reports using Excel 2010, and multiple pivot charts. When I generate reports I can not set the colors of the pivot chart series to a static value. Some times "Pass" series displayed as "RED" and this creates confusion.

我尝试使用下面的代码来强制更改系列的颜色:

I try to use the code below to force to change the colors on the series:

Sheets("PSD").Select
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.SeriesCollection(1).Select
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(0, 176, 80)
    .Transparency = 0
    .Solid
End With

该代码的问题是 SeriesCollection(1)并不总是与我想要的序列相同，当我将代码更新为SeriesCollection("Pass")时，它将无法正常工作.

The problem with the code is that SeriesCollection(1) is not always the same series I want and when I update the code as SeriesCollection("Pass"), it does NOT work.

我需要找到一种方法来按名称引用 SeriesCollection ，如果没有，我可以继续使用 On Error Resume Next ，无需进行检查.

I need to find a way to refer the SeriesCollection by name, and if it does NOT there I can continue using On Error Resume Next no need to check it.

推荐答案

要通过名称获取系列的句柄，您可以执行以下操作:

To get a handle on a series by it's name you can do this:

Sub cht()
    Dim cht As Chart
    Set cht = Sheets("PSD").ChartObjects("Chart 5").Chart

    Dim ss As Series
    Set ss = cht.SeriesCollection("Pass")

    With ss.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 176, 80)
    End With
End Sub

---

之前:

---

Before:

之后:

这篇关于需要使用VBA更改数据透视图上的颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！