使用VBA根据类别标签更改条形颜色 [英] Changing Bar colors using VBA based on category label

问题描述

我在excel中有一个VBA代码，可以更改条形图的颜色，但不适用于类别系列.

I have a VBA code in excel to change colors of bar graph but its not working for category series.

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.

对于所有条形，我想要一种颜色(绿色)，除了两个条形分别表示两个数据点(Average1和average2)；这些应该是不同的颜色.谁能告诉我如何使用VBA做到这一点?

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?

推荐答案

Jesse的答案通常是最干净的方法它.

Jesse's answer is often the cleanest way to do it.

但是，要使它们必须具有不同色标的色条位于不同系列上"并不准确(我强调).您可以可以在一个系列中混合和匹配颜色.例如，这会使第一个系列的第二个条变为红色:

However, it is not accurate that "to have different colored bars they must be on different series" (my emphasis). You can mix 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)

您可以使用它来执行各种巧妙的技巧，例如突出显示超过某个阈值的条形图，与leap年相关联，等等.您当然可以选择以这种方式突出显示您的average1和average2值.

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.

如果要更改具有给定特征的点的颜色，则必须遍历所有点，直到找到具有该特征的点.例如，如果您想将类别(XValue)为"avg"的点涂成红色，则可以执行以下操作:

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:

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

这篇关于使用VBA根据类别标签更改条形颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！