inserting a vertical line in a specific point on the horizontal axis

Hi,

I am trying to figue out how to put a vertical line on just certain tick marks on the horizontal axis.

See attachment.

Each number represents a week.

I want to be able to have a vertical line all the way up every fourth week which would represent a period.


Any suggestions.

Thanks

There are at least a couple of ways to do this. You could use a macro to insert a line at every fourth point, or you could add a column series that shows a value only if the week is divisible by four, something like this (asuming your week numbers data for the x axis start with value "1" in cell A2)

=IF(A2/4=ROUND(A2/4,0),1.2,NA())

then set the chart type to column and apply the maximum gap width

The latter of ur suggestion would not apply for it does not start in A2. This vertical line(s) would be duplicated over many similar charts. Would a macro be more efficent and if so I am not very knowledgeable about creating one. I know how to run one. Any website suggestions on how to learn to make one or any assistance from you would be greatly appreciated.

Thanks

Hi
if you change the A2 in the formula to the address of the cell with your first label, then copy the formula, it should work.
Another simple way to do this would be to add major gridlines to the x axis, and set the increment between them to 4.
If for some reason this doesn't work for you, and you want to use a macro instead, here is one which should insert a vertical line at every fourth point in your chart:

Sub chart_add_vertical_lines()
Dim PLeft As Variant, XInc As Variant, n As Variant, tt As Variant, YTop As Variant, YLow As Variant

XInc = 4 ' increment on x axis between lines
YTop = ActiveChart.Axes(2).Top 'set top of line equal to top of Y axis
YLow = ActiveChart.Axes(2).Height + YTop 'set bottom of line equal to bottom of Y axis

For n = XInc To ActiveChart.SeriesCollection(1).Points.Count Step XInc
    PLeft = ActiveChart.SeriesCollection(1).Points(n).Left
    ActiveChart.Shapes.AddConnector(msoConnectorStraight, PLeft, YTop, PLeft, YLow).Select
    'set line colour and width:
    With Selection.ShapeRange.Line
        .ForeColor.RGB = RGB(0, 0, 0)
        .Weight = 0.1
    End With
Next n

End Sub

if you want to change the increment between lines, change the XInc = 4 to the increment you want
If you want to change the colour or line width, adjust the RGB or weight
Because this inserts shapes into your graph, the lines will not adjust if you do something that changes the chart's dimensions (resizing, adding a title etc). If you do this, you will have to delete the lines and run the macro again.

To delete the shapes you could use this macro - but beware, it will delete all shapes including textboxes etc:

Sub chart_delete_shapes()
ActiveChart.Shapes.SelectAll
Selection.Delete
End Sub

to use the macros, copy them from here and in excel press Alt+F11 to enter the visual basic area, select insert > module if you don't already have modules in the workbook, then paste the code from here