Add Boarders with VBA

Adding boards around a selection in VBA is a useful program if you want a shortcut to your menu.  I was asked to produce some code which allowed the boarder to show through when colour was added to a selection of cells.  If you make a selection and turn the macro recorder on and there will be 38 lines of code produced to get borders inside, outside, horizontal and vertical.  That is a complete boarder around every cell.  When you add interior colour in Excel the borders tend to disappear so adding them back gives the look as follows;

Below is the recorded code which will produce the borders alone.

Sub Macro1() 'Recorded VBA Macro to colour cells.

Selection.Borders(xlDiagonalDown).LineStyle=xlNone
Selection.Borders(xlDiagonalUp).LineStyle=xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle=xlContinuous
.ColorIndex=0
.TintAndShade=0
.Weight=xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle=xlContinuous
.ColorIndex=0
.TintAndShade=0
.Weight=xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle=xlContinuous
.ColorIndex=0
.TintAndShade=0
.Weight=xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle=xlContinuous
.ColorIndex=0
.TintAndShade=0
.Weight=xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle=xlContinuous
.ColorIndex=0
.TintAndShade=0
.Weight=xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle=xlContinuous
.ColorIndex=0
.TintAndShade=0
.Weight=xlThin
End With
End Sub

This took me down a path and I ended up with the following procedure which will produce the same result.

Option Explicit

Sub AddBoarder() 'Excel VBA macro to improve recorded macro.
Dim i As Long
Dim Var As Variant

Var=Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlInsideVertical, xlInsideHorizontal)
For> i=LBound(Var) To UBound(Var)
Selection.Borders(Var(i)).Weight=xlThin
Next i
End Sub

This efficiently adds a boarder to a selection and is a nice addition to any personal macro workbook. It uses a loop to achieve the task.

The good part about being involved in a community like Ozgrid is that occasionally you will see far better solutions. Wim Gielis (Wigi), a star in the Excel community wrote to me with a more efficient solution which has no loop at all.

Sub AddBorderWigi() 'Excel VBA improvement by Wim Gielis (respect).
Selection.Borders.Weight=xlThin
End Sub

Ouch!!!! It is surprising when looking at the recorded code for the procedure and then seeing what that code can be shortened to by a guy like Wigi. To see more of his fantastic solutions, check out Wigi's site here.

http://www.wimgielis.be/

Adding the colour using Wigi's improved code can be done as follows;

Option Explicit

Sub RedInterior() 'Turns the slection Red
Selection.Borders.Weight=xlThin
Selection.Interior.Color=vbRed
End Sub


Attached is an Excel file to show workings and the VBA procedure.