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
JCJGAsked:
Who is Participating?
 
Saqib Husain, SyedEngineerCommented:
This code will automatically hide the rows which are zeroed.

To enter the code,
right-click on the sheet tab name
select view code
paste the given code in the VBA code pane
close the VBA window
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 Then
        If Target = 0 Then
            If Target.Offset(, -1) <> "Middle" And Target.Offset(, -1) <> "End" And Target.Offset(, -1) <> "Start" Then
                Target.EntireRow.Hidden = True
            End If
        End If
    End If
End Sub

Open in new window

0
 
Saqib Husain, SyedEngineerCommented:
You can use separate columns for plotting positive and negative numbers. See attached.
Copy-of-Waterfall-Chart.xlsx
0
 
Elton PascuaCommented:
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

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
JCJGAuthor Commented:
Hi techfantic - the code works except it changed the color of the Start and End bars.  Also I prefer the darker shade of green.
0
 
JCJGAuthor Commented:
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?
0
 
Saqib Husain, SyedEngineerCommented:
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?
0
 
JCJGAuthor Commented:
Please look at the first and second tabs of the attached file.  Thanks.
Waterfall-Chart-v2.xlsx
0
 
Saqib Husain, SyedEngineerCommented:
0
 
JCJGAuthor Commented:
Great!  It works.  I assume the zero value categories cannot be hidden unless we use some codes.
0
 
JCJGAuthor Commented:
Thanks for all your help!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.