# Applying same formatting to multiple borders in Excel VBA?

Is there a better way to format the cells with borders than what you get when you record a macro? For example, I want to add borders to a cell range. The recorded code is:

Range("A1:C19").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.Weight = xlThin
End With


Can this be shortened?

• Yes, it can be shortened but it depends on which border attributes you want to change. More information on how you want the borders to be formatted will be helpful. – J. Garth Mar 1 '17 at 17:34
• Like in the code, the top, bottom, right and left. – Selrac Mar 1 '17 at 17:38

If the question is "how do I apply the same formatting to multiple borders?" then the following is one way:

Dim the_borders As Variant
the_borders = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlInsideVertical, xlInsideHorizontal)
' Or whatever xlEdge* constants you want to list

Dim idx As Long
For idx = LBound(the_borders) To UBound(the_borders)
With Selection.Borders(the_borders(idx))     ' Process the right border
.LineStyle = xlContinuous
.ColorIndex = 0
.Weight = xlThin
End With
Next idx


The As Variant / Array combo gives you an array of the border IDs you specify, running from LBound(the_borders) to UBound(the_borders). Then, within the loop, borders(idx) is the XlBordersIndex you can pass to Selection.Borders.

You can also use. I added the selection range so you get intellisense.

Dim selectionRange As Range
Set selectionRange = Selection

Dim theLineStyle As XlLineStyle
theLineStyle = xlContinuous

With selectionRange
.BorderAround theLineStyle
.Borders(xlInsideHorizontal).LineStyle = theLineStyle
.Borders(xlInsideVertical).LineStyle = theLineStyle
end With

• Not bad, although I would recommend using Selection.Range instead of selectionRange --- you will still have IntelliSense and you won't need an extra variable :) . – cxw Mar 1 '17 at 21:32