Forum Discussion
Add gridline at only the 0 axis for Waterfall chart
Hello,
I have a waterfall chart that reads dynamic data that could potentially cross the 0 axis line. I want to avoid gridlines on the whole chart, but would like to add one for the 0 axis to highlight when the chart goes negative. I thought about trying a combo chart but waterfalls don't allow this. Is there any other way to implement this, potentially with VBA?
- Sub AddZeroAxisLine()
Dim cht As Chart
Dim zeroLine As Shape
Dim chartArea As ChartArea
Dim zeroLineY As Double
' Change the sheet name and chart name as needed
Set cht = ThisWorkbook.Sheets("Sheet1").ChartObjects("Chart 1").Chart ' Update with your chart's sheet and name
' Remove any existing zero line
On Error Resume Next
For Each zeroLine In cht.Shapes
If zeroLine.Name = "ZeroAxisLine" Then zeroLine.Delete
Next zeroLine
On Error GoTo 0
' Calculate the Y value for the zero axis line
Set chartArea = cht.ChartArea
zeroLineY = cht.PlotArea.InsideHeight / 2 ' This assumes the zero line is in the middle of the chart area
' Draw the zero axis line
Set zeroLine = cht.Shapes.AddLine(BeginX:=0, BeginY:=zeroLineY, EndX:=cht.PlotArea.InsideWidth, EndY:=zeroLineY)
With zeroLine
.Name = "ZeroAxisLine"
.Line.ForeColor.RGB = RGB(255, 0, 0) ' Red color for the zero line
.Line.Weight = 1.5 ' Line weight
.Line.DashStyle = msoLineSolid ' Solid line style
.ZOrder msoBringToFront ' Bring line to the front
End With
End Sub
