Plotting an arrow in excel using VBA

[Ray.Mason]

Hi Guys!

I am a little stuck in my macro build. I don't know what I'm trying to do is achievable. I need a macro that looks into a cell (e.g A2 and G2). These cells have start and end dates. These dates are then compared to merged cells starting column MN row 6 all the way into calendar which is plotted horizontally. Therefore when start date is matched we plot a line all the way to end date, preferribly double arrow. <-------------------->

Any ideas?

[Bob Phillips]

This should get you started

[VBA]Dim arrow As Shape
Dim startCell As Range
Dim endCell As Range

With ActiveSheet

On Error Resume Next
.Shapes("dateline").Delete
On Error GoTo 0

Set startCell = .Rows(6).Find(.Range("A2").Value)
Set endCell = .Rows(6).Find(.Range("G2").Value)
Set arrow = .Shapes.AddConnector(msoConnectorStraight, startCell.Left, startCell.Top - 10, endCell.Left + endCell.Width, startCell.Top - 10)
arrow.Line.BeginArrowheadStyle = msoArrowheadOpen
arrow.Line.EndArrowheadStyle = msoArrowheadOpen
arrow.Name = "dateline"
End With[/VBA]

[Ray.Mason]

Thanks xld. That will indeed get me started! Will let you know on progress.

Many thanks!

[Bob Phillips]

One small change to span the end of the final merged cell

[VBA]Dim arrow As Shape
Dim startCell As Range
Dim endCell As Range

With ActiveSheet

On Error Resume Next
.Shapes("dateline").Delete
On Error GoTo 0

Set startCell = .Rows(6).Find(.Range("A2").Value)
Set endCell = .Rows(6).Find(.Range("G2").Value)
Set arrow = .Shapes.AddConnector(msoConnectorStraight, _
startCell.Left, _
startCell.Top - 10, _
endCell.Left + endCell.MergeArea.Width, _
startCell.Top - 10)
arrow.Line.BeginArrowheadStyle = msoArrowheadOpen
arrow.Line.EndArrowheadStyle = msoArrowheadOpen
arrow.Name = "dateline"
End With[/VBA]