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  quot DATA quot   endRow   DT Range  quot F quot   amp  Rows Count  End xlUp  Row result   3  For I   2 To endRow     If DT Cells I  6  Value   Range  quot B1 quot   Value Then         Range  quot A quot   amp  result    DT Cells I  6  Value         Range  quot B quot   amp  result    DT Cells I  1  Value         Range  quot C quot   amp  result    DT Cells I  24  Value         Range  quot D quot   amp  result    DT Cells I  37  Value         Range  quot E quot   amp  result    DT Cells I  3  Value         Range  quot F quot   amp  result    DT Cells I  15  Value         Range  quot G quot   amp  result    DT Cells I  12  Value         Range  quot H quot   amp  result    DT Cells I  40  Value         Range  quot I quot   amp  result    DT Cells I  23  Value         result   result   1     End If Next I

User · Accepted Answer

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

User · Answer

For adding borders try this  for example   Range  C11   Borders xlEdgeRight  LineStyle   xlContinuous Range  A15 D15   Borders xlEdgeBottom  LineStyle   xlContinuous   Hope that syntax is correct because I ve done this in C

User · Answer

xlWorkSheet Cells 1  1  Borders Excel XlBordersIndex xlEdgeRight  LineStyle   Excel XlDataBarBorderType xlDataBarBorderSolid xlWorkSheet Cells 1  1  Borders Excel XlBordersIndex xlEdgeLeft  LineStyle   Excel XlDataBarBorderType xlDataBarBorderSolid xlWorkSheet Cells 1  1  Borders Excel XlBordersIndex xlEdgeBottom  LineStyle   Excel XlDataBarBorderType xlDataBarBorderSolid xlWorkSheet Cells 1  1  Borders Excel XlBordersIndex xlEdgeTop  LineStyle   Excel XlDataBarBorderType xlDataBarBorderSolid

User · Answer

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

User · Answer

Here s another way  Sub testborder        Dim rRng As Range      Set rRng   Sheet1 Range  B2 D5         Clear existing     rRng Borders LineStyle   xlNone       Apply new borders     rRng BorderAround xlContinuous     rRng Borders xlInsideHorizontal  LineStyle   xlContinuous     rRng Borders xlInsideVertical  LineStyle   xlContinuous  End Sub

User · Answer

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   amp  result   Call SetRangeBorder DT Cells I  6   Call SetRangeBorder Range  A3 I   amp  endRow

User · Answer

You can also include this task within another macro  without opening a new one   I don t put Sub and end Sub  because the macro contains much longer code  as per picture below  With Sheets  1 PL   Range  EF1631 JJ1897       With  Borders      LineStyle   xlContinuous      Color   vbBlack      Weight   xlThin     End With    enter image description here  1   1 End With

[excel] Border around each cell in a range

Examples related to excel

Examples related to vba