Border around each cell in a range
You only need a single line of code to set the border around every cell in the range:
Range("A1:F20").Borders.LineStyle = xlContinuous
It's also easy to apply multiple effects to the border around each cell.
For example:
Sub RedOutlineCells()
Dim rng As Range
Set rng = Range("A1:F20")
With rng.Borders
.LineStyle = xlContinuous
.Color = vbRed
.Weight = xlThin
End With
End Sub
I have a set of 15 subroutines I add to every Coded Excel Workbook I create and this is one of them. The following routine clears the area and creates a border.
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
The following can be called with any range as parameter:
Option Explicit
Sub SetRangeBorder(poRng As Range)
If Not poRng Is Nothing Then
poRng.Borders(xlDiagonalDown).LineStyle = xlNone
poRng.Borders(xlDiagonalUp).LineStyle = xlNone
poRng.Borders(xlEdgeLeft).LineStyle = xlContinuous
poRng.Borders(xlEdgeTop).LineStyle = xlContinuous
poRng.Borders(xlEdgeBottom).LineStyle = xlContinuous
poRng.Borders(xlEdgeRight).LineStyle = xlContinuous
poRng.Borders(xlInsideVertical).LineStyle = xlContinuous
poRng.Borders(xlInsideHorizontal).LineStyle = xlContinuous
End If
End Sub
Examples:
Call SetRangeBorder(Range("C11"))
Call SetRangeBorder(Range("A" & result))
Call SetRangeBorder(DT.Cells(I, 6))
Call SetRangeBorder(Range("A3:I" & endRow))