excel vba changing bar chart color for a data poin

问题:

---

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.

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

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.

Any thoughts on what I'm doing wrong?

Thanks in advance for your help.

回答1:

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.

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

回答2:

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:

Conditional Formatting in Excel Charts