VBA To Format Border Lines

[TkdKidSnake]

Hi all,

I am really struggling to use VBA to automatically add borders - see below:

Data is always from column A to S but varies in length

Task 1 - Add an extra think border around the whole worksheet (Code is below)
So essentially I just need a way of selecting all the cells from A1 to the last populate cell in column A and then across to column S

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

Task 2, In the same selected area as above I also want to add a dividing line when the data value changes from B2 through to the end of the document
So fundamentally if the were 3 rows with 841 in and then it changed to 842 I want a think line to be placed in between to separate them.

So assuming the code below would be part of it

With Selection.Borders(xlInsideVertical)        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

If anyone can help it would be greatly appreciated.

 

[Fluff]

For the first part try

Sub TkdKidSnake()
   With Range("A1").CurrentRegion
      .Borders.LineStyle = xlNone
      .BorderAround , xlThick
      .Borders(xlInsideVertical).LineStyle = xlThin
   End With
End Sub

 

[TkdKidSnake]

This worked an absolute treat Fluff thank you

For the first part try

Sub TkdKidSnake()
   With Range("A1").CurrentRegion
      .Borders.LineStyle = xlNone
      .BorderAround , xlThick
      .Borders(xlInsideVertical).LineStyle = xlThin
   End With
End Sub

 

[TkdKidSnake]

Can anyone help me with task 2 please

 

[Fluff]

Ok, this should do part 2 as well

Sub TkdKidSnake()
   Dim Cl As Range
   Dim UsdCols As Long
   
   With Range("A1").CurrentRegion
      UsdCols = .Columns.Count
      .Borders.LineStyle = xlNone
      .BorderAround , xlThick
      .Borders(xlInsideVertical).Weight = xlThin
   End With
   For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
      If Cl.Value <> Cl.Offset(1).Value Then
         Cl.Offset(, -1).Resize(, UsdCols).Borders(xlEdgeBottom).Weight = xlThick
      End If
   Next Cl
End Sub

 

[TkdKidSnake]

Excellent Fluff many thanks works an absolute treat, I can stop tearing my hair out now

Ok, this should do part 2 as well

Sub TkdKidSnake()
   Dim Cl As Range
   Dim UsdCols As Long
   
   With Range("A1").CurrentRegion
      UsdCols = .Columns.Count
      .Borders.LineStyle = xlNone
      .BorderAround , xlThick
      .Borders(xlInsideVertical).Weight = xlThin
   End With
   For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
      If Cl.Value <> Cl.Offset(1).Value Then
         Cl.Offset(, -1).Resize(, UsdCols).Borders(xlEdgeBottom).Weight = xlThick
      End If
   Next Cl
End Sub

 

[Fluff]

You're welcome & thanks for the feedback

 

[TkdKidSnake]

No worries credit where credit is due buddy

You're welcome & thanks for the feedback