?
Solved

Excel Waterfall Chart - Automatic color change based on value

Posted on 2012-08-14
10
Medium Priority
?
3,008 Views
Last Modified: 2012-08-24
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
0
Comment
Question by:JCJG
  • 5
  • 4
10 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38294297
You can use separate columns for plotting positive and negative numbers. See attached.
Copy-of-Waterfall-Chart.xlsx
0
 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38294457
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
 

Author Comment

by:JCJG
ID: 38294838
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:JCJG
ID: 38294874
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38295229
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
 

Author Comment

by:JCJG
ID: 38297791
Please look at the first and second tabs of the attached file.  Thanks.
Waterfall-Chart-v2.xlsx
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38298665
0
 

Author Comment

by:JCJG
ID: 38299525
Great!  It works.  I assume the zero value categories cannot be hidden unless we use some codes.
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 total points
ID: 38300412
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
 

Author Closing Comment

by:JCJG
ID: 38331908
Thanks for all your help!!!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question