Border around each cell in a range

Question

I am trying to create a simple function that will add borders around every cell in a certain range. Using the wonderful recording this generates a ton of code which is quite useless. The code below will display a 'table' of data, around each cell in this range I would like to add a border. Online I haven't been able to find a simple or clear answer for this.

All help is much appreciated!

Set DT = Sheets("DATA") endRow = DT.Range("F" & Rows.Count).End(xlUp).Row result = 3  For I = 2 To endRow     If DT.Cells(I, 6).Value = Range("B1").Value Then         Range("A" & result) = DT.Cells(I, 6).Value         Range("B" & result) = DT.Cells(I, 1).Value         Range("C" & result) = DT.Cells(I, 24).Value         Range("D" & result) = DT.Cells(I, 37).Value         Range("E" & result) = DT.Cells(I, 3).Value         Range("F" & result) = DT.Cells(I, 15).Value         Range("G" & result) = DT.Cells(I, 12).Value         Range("H" & result) = DT.Cells(I, 40).Value         Range("I" & result) = DT.Cells(I, 23).Value         result = result + 1     End If Next I 

Answer 1

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 

Answer 2

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))