Changing Waterfall chart colour based on point value

Question

I would like to change the colour of waterfall chart based on chart value.
For example, if the value negative, put red colour. If value is positive, put green colour.
I got below code from excel vba changing bar chart color for a data point based on point value

This code works with bar chart. However, I cannot use it with waterfall as it shown error "Object doesn't support this action"

Does anyone know how to config this to use with waterfall? Any suggestion would be highly appreciated!

Thanks,

 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

I would have two series in the chart, one red & the other green. Then control the data on the sheet as to which cells show data. Similar to what I did here: https://stackoverflow.com/a/69077964/4961700 — Solar Mike, Mar 25 '22 at 14:19
Arent the values already formatted according to their sign? Positive are blue and negative are orange. — Jon Peltier, Apr 05 '22 at 01:31
You can select the legend entries for Increase and Decrease (click once to select the legend, again to select the specific legend entry, then Ctrl+1 to open the Format Legend Entry task pane, which exposes the tools to change colors. — Jon Peltier, Apr 05 '22 at 01:39

score 1 · Answer 1 · edited Mar 29 '22 at 00:12

Here is the code I used. It works with Waterfall

Sub cht_loop()
    Dim i As Integer
    Dim cht As Chart
    Set cht = Sheets("sheet1").ChartObjects("Chart 10").Chart
    
    For i = 1 To 5
        If Sheets("Sheet1").Range("B" & i + 1).Value < 0 Then
        With cht.SeriesCollection(1).Points(i).Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(192, 0, 0)
        End With
        Else
        With cht.SeriesCollection(1).Points(i).Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(0, 176, 80)
        End With
        End If
    Next i
End Sub

score 0 · Answer 2 · answered Mar 25 '22 at 14:37

0

So, a quick example:

Just to show what I meant in my comment.

answered Mar 25 '22 at 14:37

Solar Mike

7,156
4
17
32

Thanks much for the suggestion! but I would need this to use in report template which all the source data is already set up (so will take much effort to re-set all the thing). at the end I came up with VBA code, I will put it in another comment. anyway thanks again for your help! – PJT Mar 28 '22 at 14:10

Changing Waterfall chart colour based on point value

2 Answers2

Linked