drawing a line with VBA

[plost33]

How would i have VBA draw a line between a two ranges? say i have a named range of "start" and another name range "stop". i want to draw a red line between them. how can i do that with code. this is beggining my education for a much larger project I want to do later in the future.

 

[TheNoocH]

see if something like this would work...

Sub LeftBorder()
    Application.Goto Reference:="stop"
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Color = -16776961
        .TintAndShade = 0
        .Weight = xlThick
    End With
End Sub

 

[plost33]

how does that refrence the named ranges?

 

[VoG]

Try

Sub AddLine()
Dim l1 As Long, l2 As Long, r1 As Long, r2 As Long
l1 = Range("Start").Left
l2 = Range("Start").Top
r1 = Range("Stop").Left
r2 = Range("Stop").Top
With ActiveSheet.Shapes.AddLine(l1, l2, r1, r2).Line
    .ForeColor.RGB = RGB(255, 0, 0)
End With
End Sub
[

 

[plost33]

vog, totally diffrent questrion but you seem to be the smartest guy on here with time format crap. how come in [h]:mm format 24:00 shows as 1/1/1900 12:00:00AM in the formula bar? and more importantly how can I have it show just as 12:00:00AM??

 

[VoG]

vog, totally diffrent questrion but you seem to be the smartest guy on here with time format crap. how come in [h]:mm format 24:00 shows as 1/1/1900 12:00:00AM in the formula bar? and more importantly how can I have it show just as 12:00:00AM??

(a) Did it work?

(b) You need to start a new thread or continue in your original.

 

[plost33]

it did work. thanks.

 

[plost33]

vog, here is the other thread:

http://www.mrexcel.com/forum/showthread.php?t=398071

 

[plost33]

vog, how would i change that code to draw a line from the bottom left corner of the range rather than top left, on the start range??

 

[VoG]

Try

Sub AddLine()
Dim l1 As Long, l2 As Long, r1 As Long, r2 As Long
l1 = Range("Start").Left
l2 = Range("Start").Top + Range("Start").RowHeight
r1 = Range("Stop").Left
r2 = Range("Stop").Top
With ActiveSheet.Shapes.AddLine(l1, l2, r1, r2).Line
    .ForeColor.RGB = RGB(255, 0, 0)
End With
End Sub