excel vba 根据点值更改数据点的条形图颜色

excel vba changing bar chart color for a data point based on point value

提问by user1899231

---

---

I have a some charts that in which the X Values are text and Y Values are numerical. I want to color each bar Red if the Y value for the bar is less than zero, Green if greater than or equal to zero. Also, if the X value of the bar is "NET CHANGE", I need the bar to be yellow. I followed the directions in a previous StackOverflow thread here: Changing Bar colors using VBA based on category label.

我有一些图表，其中 X 值是文本，Y 值是数字。如果条的 Y 值小于零，我想将每个条着色为红色，如果大于或等于零，则为绿色。此外，如果条形的 X 值是“净变化”，我需要条形为黄色。我按照上一个 StackOverflow 线程中的说明进行操作：Changing Bar colors using VBA based on category label。

I'm getting Run-time error 451 Property let procedure not defined and property get procedure did not return an object.

我收到运行时错误 451 未定义属性让过程并且属性获取过程未返回对象。

My code is below:

我的代码如下：

For chartIterator = 1 To ActiveSheet.ChartObjects.count

    For pointIterator = 1 To ActiveWorkbook.Sheets("Sheet1").ChartObjects(chartIterator).Chart.SeriesCollection(1).Points.count
        If ActiveWorkbook.Sheets("Sheet1").ChartObjects(chartIterator).Chart.SeriesCollection(1).Values(pointIterator) >= 0 Then
            ActiveWorkbook.Sheets("Sheet1").ChartObjects(chartIterator).Chart.SeriesCollection(1).Points(pointIterator).Interior.Color = _
                RGB(146, 208, 80)
        Else
            ActiveWorkbook.Sheets("Due To Chart").ChartObjects(chartIterator).Chart.SeriesCollection(1).Points(pointIterator).Interior.Color = _
                RGB(255, 0, 0)
        End If
    Next pointIterator

Next chartIterator

The error comes up at the IF statement. I also tried .Points(pointIterator).Value, which got me a "property or method not defined for this object" error.

错误出现在 IF 语句中。我还尝试了 .Points(pointIterator).Value，这让我得到了“未为此对象定义的属性或方法”错误。

Any thoughts on what I'm doing wrong?

对我做错了什么有任何想法吗？

Thanks in advance for your help.

在此先感谢您的帮助。

采纳答案by chuff

You are running into trouble in your use of SeriesCollection(1).Values, which you are treating as an array that you can iterate over. Instead, this is a function that return the values of the points in the SeriesCollection.

您在使用 SeriesCollection(1).Values 时遇到了麻烦，您将其视为可以迭代的数组。相反，这是一个返回 SeriesCollection 中点的值的函数。

What's needed is to assign the results of the function to an array variable, then iterate over the array to test whether the values in the array are greater than or less than zero. Then, you can assign the colors to the chart points.

需要的是将函数的结果分配给一个数组变量，然后遍历数组以测试数组中的值是大于零还是小于零。然后，您可以为图表点分配颜色。

This code should do the trick:

这段代码应该可以解决问题：

    Sub color_chart()

    Dim chartIterator As Integer, pointIterator As Integer, _
        seriesArray() As Variant

    For chartIterator = 1 To ActiveSheet.ChartObjects.Count
        seriesArray =  ActiveWorkbook.Sheets("Sheet1").ChartObjects(chartIterator). _
                       chart.SeriesCollection(1).Values

        For pointIterator = 1 To UBound(seriesArray)             

           If seriesArray(pointIterator) >= 0 Then
               ActiveWorkbook.Sheets("Sheet1").ChartObjects(chartIterator). _  
               chart.SeriesCollection(1).Points(pointIterator).Interior.Color = _
               RGB(146, 208, 80)
           Else
               ActiveWorkbook.Sheets("Sheet1").ChartObjects(chartIterator). _
               chart.SeriesCollection(1).Points(pointIterator).Interior.Color = _
               RGB(255, 0, 0)
           End If

        Next pointIterator

    Next chartIterator

    End Sub

回答by Jon Peltier

Here is an alternative that does not require VBA, and works dynamically as formulas update. Check out the "Conditional Formatted Bar Chart" example in this tutorial:

这是一个不需要 VBA 的替代方法，并且在公式更新时动态工作。查看本教程中的“条件格式条形图”示例：

Conditional Formatting in Excel Charts

Excel 图表中的条件格式