Simplify macro to show grid lines

[Mike K]

I recorded a macro to add grid lines to a range of cells, and then I
converted the macro to a more readable format:

Public Sub ShowGridLines(rng As Range)
With rng
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone

With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With .Borders(xlEdgeTop)...


However, I thought there may be a way to simplify it even more, using
the Borders collection:

Dim b as Border
For Each b in rng.Borders
b.LineStyle = xlContinuous
b.Weight = xlThin
b.ColorIndex = xlAutomatic
Next

This would reduce all that code to six lines.


The problem is this code adds every border, including xlDiagonalDown
and xlDiagonalUp. Does anyone know how to filter these two borders
from the collection? The recorded macro explicity sets these line
styles to "xlNone" (see above).

 

[Tom Ogilvy]

Possibly

Sub efg()
Dim b As Border
i = 0
Set rng = ActiveCell
For Each b In rng.Borders
i = i + 1
If i <> 5 And i <> 6 Then
b.LineStyle = xlContinuous
b.Weight = xlThin
b.ColorIndex = xlAutomatic
End If
Next
End Sub