asked on

Excel Waterfall Chart - Automatic color change based on value

Hi, I'd like to automate the bar colors in the attached waterfall chart. For example, if the value goes down (negative) I would like the bar to be green and red bar for value that goes up (positive). I am doing this manually.

Can you help me to automate it so the color will change based on value? Thanks.
Waterfall-Chart.xlsx

Saqib Husain

You can use separate columns for plotting positive and negative numbers. See attached.
Copy-of-Waterfall-Chart.xlsx

Elton Pascua

This should get you started.

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim pts As Points
    Dim i As Integer
    
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("OPEX WF")
 
    Set pts = ws.ChartObjects(1).Chart.SeriesCollection(2).Points
    
    For i = 1 To pts.Count
        If ws.Range("B" & i + 2).Value > 0 Then
            pts(i).Interior.Color = vbRed
        Else
            pts(i).Interior.Color = vbGreen
        End If
    Next i

Open in new window

JCJG

ASKER

Hi techfantic - the code works except it changed the color of the Start and End bars. Also I prefer the darker shade of green.

JCJG

ASKER

Hi ssaqibh - the separate columns approach works for me. Thanks.

One more question: The bar will disappear if any one category is zero. So I hide the zero lines but the "middle" blue bar disappear and shift to the right. Is there a way to fix this and for zero lines not show up in the chart?

Saqib Husain

For the middle bar you can add yet another column.

I am not sure I understand your column fully. Can you demonstrate with an example?

JCJG

ASKER

Please look at the first and second tabs of the attached file. Thanks.
Waterfall-Chart-v2.xlsx

Saqib Husain

Try this file
Copy-of-Waterfall-Chart-v2.xlsx

JCJG

ASKER

Great! It works. I assume the zero value categories cannot be hidden unless we use some codes.

ASKER CERTIFIED SOLUTION

Saqib Husain