All Borders equivalent in Excel for a range

I would think there is a way in VBA to put the 4 wall borders for every cell in a range, but I have yet to figure out or find one. Basically I want a range such as (A1:R780) to all have their own square.

Crazyd

Posted 2016-03-15T01:20:50.243

Reputation: 59

possible duplicate http://stackoverflow.com/questions/13121425/border-around-each-cell-in-a-range

– Musselman – 2016-03-15T01:43:46.813

Simpler question didn't look on Stack Over Flow due to not really a programming question as I see it. – Crazyd – 2017-12-05T08:27:59.623

Answers

I don't know a more efficient way than this.

With Range("A1:R780")
    .Borders(xlInsideVertical).LineStyle = xlContinuous
    .Borders(xlInsideHorizontal).LineStyle = xlContinuous
    .BorderAround xlContinuous
End with

Tough I belive this would also work.

Range(“A1:R780").Borders.LineStyle = xlContinuous

damik_

Posted 2016-03-15T01:20:50.243

Reputation: 26

Thanks I actually found a much more complex way of doing it.... probably 10 minutes before you answered... just now checked to see if there was an easier way... I basically had 6 Commands to do the same basic thing.

The second one didn't work, but the with Range did work. – Crazyd – 2016-03-18T15:59:48.533

How I did it... The on error line is in case the area doesn't have Vertical or Horizontal lines. Honestly originally I didn't use BorderAround I used xlEdgeBottom, xlEdgeTop, xlEdgeLeft, xlEdgeRight.

I had to use call 5 times aka reason for making it a subroutine, plus they were dynamic.

Sample Call

Call BoxIt(Range("A1:z25"))

Subroutine

Sub BoxIt(aRng As Range)
On Error Resume Next

    With aRng

        'Clear existing
        .Borders.LineStyle = xlNone

        'Apply new borders
        .BorderAround xlContinuous, xlThick, 0
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .Weight = xlMedium
        End With
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .Weight = xlMedium
        End With
    End With

End Sub

Crazyd

Posted 2016-03-15T01:20:50.243

Reputation: 59