Vertical Line in an XY Chart at Today's Date

[C. Bailey]

Is there an easy way to create a vertical line in an XY chart at today's
date assuming the 'X - axis' is populated with dates? Or any other way to
easily distinguish the history from the future? It would be neat if I could
change the background color to the left and right of the now() date.

I can create the vertical line using worksheet function and by adding
another series. It's just messy in my particular spreadsheet because of
other VBA code.

Thank you,
Chris

 

[Joel]

Here is some code I wrote for work a few years ago.


InsideHeight = Satellite_Chart.PlotArea.InsideHeight
InsideLeft = Satellite_Chart.PlotArea.InsideLeft
InsideTop = Satellite_Chart.PlotArea.InsideTop
InsideWidth = Satellite_Chart.PlotArea.InsideWidth

LineLocation = (TodaysDate - StartDate) / (CurrentDate - StartDate)


Satellite_Chart.Shapes.AddLine(InsideLeft + (LineLocation * InsideWidth), _
InsideTop, _
InsideLeft + (LineLocation * InsideWidth), _
InsideTop + InsideHeight).Select

Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
Selection.ShapeRange.Line.Visible = True

 

[Jon Peltier]

That line will never keep still. There are two ways to make a vertical line
that will stay where you put it. Both involve adding another series to the
chart. In one, add a series consisting of two points, both using the data
where you want it as X, the other using the minimum and maximum Y values.
Format this as an XY series with not markers but a connecting line. The
other approach needs a one-point series, where X = the date, and Y = either
the min or max, format the series to show no marker or lines, and add a
vertical error bar with a magnitude equal to the Y axis max minus min.

The techniques are as shown in this page for a column or line chart, but
easier for an XY chart:

http://peltiertech.com/Excel/Charts/AddLine.html

- Jon