Borders in VBA

[SonicBoomGolf]

Simple question for the pros here. Is there a way to shorted the code needed to put borders around cells? Attached is some code I recorded with the macro recorder which puts borders around cells.

With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With


I am trying to use something similar to the following to shorten the code.

Dim formatRng As Range
Set formatRng = Application.Union(xlEdgeLeft, xlEdgeRight, xlEdgeTop, xlEdgeBottom)
With formatRng.Borders
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With


Any ideas??

 

[Damon Ostrander]

Hi trackman69,

Unfortunately, the Borders collection does not accept an Array argument, as many other collections do. However, you can shorten the code considerably by looping through the borders:

Dim Border As Integer
Dim MyRange As Range

Set MyRange = [B4:Q10] 'range of cells to apply border properties to

For Border = xlEdgeLeft To xlEdgeBottom
With MyRange.Borders(Border)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Next Border


Damon

 

[SonicBoomGolf]

Good to know. I can definetly use this code. Thanks for the help!!

 

[Norie]

You could just use this:

With Selection.Borders
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
End With