CODEWITHSUNDEEP
vbaexcel
user3798267
user3798267

Reputation: 7

Creating a line in an excel chart which always shoots a red line through todays date in VBA

I have a chart which I would like to always run a line through today's date after I run a Macro I am working with. I would also like it to say Today right above the chart and above the red line. I would like to code this out in VBA but I don't really know where to begin. I have the chart Does this make sense? Thanks.

Upvotes: 0

Views: 707

Answers (1)

asp8811
asp8811

Reputation: 803

Read through this and combine with =TODAY().

Edit: The specific formula you'd be using for your column of vertical lines is 

  =IF('cell in same row containing date'=TODAY(),MAX('range of your data'),"")

This formula will return the max from your list of data if the date in your table is today's. If you follow the instructions for option 2 from the link and create another data series for vertical lines and put this formula in that column, you will have a vertical line that is always on today's date.

One more Edit: To address having "Today" above the line, add data labels to your "Today" series by right clicking on the series and going to "Add Data Labels." Right click on the Data Labels and go to "Format Data Labels." Go to "Number" and select Custom. Enter "Today";;; into the Format Code box and click add. Your Data Labels for your blanks should go away and your date line should have "Today" over it. Format it however you wish.

And if you want to make this part of a macro, record it and play around with the code you get until you find something that works. IMO, just having this as a part of your chart is easy enough.

Last edit I swear: If your line is too fat, right click on the series/line, go to "Format Series," change the fill to a gradient, set the rotation to 180*, and play around with the stop positions and transparencies to make the line appear thinner. That's all I've got, so if I've helped in any small way, mark this as useful.

Upvotes: 0

Related Questions