根据类别标签使用 VBA 更改条形颜色
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9486186/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Changing Bar colors using VBA based on category label
提问by Nupur
I have a VBA code in excel to change colors of bar graph but its not working for category series.
我在 excel 中有一个 VBA 代码来更改条形图的颜色，但它不适用于类别系列。
ActiveChart.SeriesCollection(1).Interior.Color = RGB(0, 153, 64)
I want to change the color of a single bar. However, the above code changes the color of all bars.
我想更改单个条的颜色。但是，上面的代码更改了所有条的颜色。
For all bars I want one color (green) except for two bars representing two data points (Average1 and average2); these should be of a different color. Can anyone please tell me how to to this with VBA?
对于所有条形，我想要一种颜色（绿色），除了代表两个数据点（Average1 和average2）的两个条形；这些应该是不同的颜色。谁能告诉我如何用 VBA 做到这一点？
回答by Jean-Fran?ois Corbett
Jesse's answeris often the cleanest way to do it.
杰西的回答通常是最干净的方法。
However, it is not accurate that "to have different colored bars they mustbe on different series" (my emphasis). You canmix and match colors within one series. For example, this makes the second bar of the first series red:
但是，“要具有不同的颜色条，它们必须位于不同的系列”（我的重点）是不准确的。您可以在一个系列中混合和匹配颜色。例如，这会使第一个系列的第二个条形变为红色：
ActiveChart.SeriesCollection(1).Points(2).Interior.Color = RGB(255, 0, 0)
You can use this to do all kinds of neat tricks, such as highlighting bars that exceed some threshold, are associated with leap years, or whatever. You could certainly choose to highlight your average1 and average2 values this way.
您可以使用它来做各种巧妙的技巧，例如突出显示超过某个阈值的条形，与闰年相关联，或其他。您当然可以选择以这种方式突出显示您的 average1 和 average2 值。
If you want to change the color for a point that has a given characteristic, then you have to loop through all points until you find a point that has that characteristic. For example, if you want to color in red the point whose category (XValue) is "avg"then you could do this:
如果要更改具有给定特征的点的颜色，则必须遍历所有点，直到找到具有该特征的点。例如，如果要将类别 ( XValue) 所在的点涂成红色，"avg"则可以执行以下操作：
Dim c As Chart
Dim s As Series
Dim iPoint As Long
Dim nPoint As Long
Set c = ActiveChart
Set s = c.SeriesCollection(1)
nPoint = s.Points.Count
For iPoint = 1 To nPoint
If s.XValues(iPoint) = "avg" Then
s.Points(iPoint).Interior.Color = RGB(255, 0, 0)
End If
Next iPoint
回答by Jesse
Your problem isn't with the VBA, to have different colored bars they must be on different series.
你的问题不在于 VBA，要有不同的颜色条，它们必须在不同的系列上。
Leave a gap in the base colored series and add the values you want colored on a second series and color that. Your data would look something like this:
在基色系列中留出空隙，并在第二个系列上添加您想要着色的值并为其着色。您的数据将如下所示：
Series | Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 | Month 7
1 10 12 15 14 10
2 17
3 18
