How to add a Thick bottom border across multiple cells every 9th row,

Question

I've found codes in other posts and other forums, but they don't seem to work for me.

I only have a basic working knowledge of VBA I can understand what it does, but creating a complex code is harder

I have a multiple worksheets A1:K## Column C has a number or a code (text) Starting with row B I want to add a thick bottom border every 9th row (so after 9, 18, 27 etc) And stop when the numbers end Restart the count for the Code(text).

Thanks in advance for any help

Here's a sample of what started with, but could not manage to properly modify it to do what I wanted.

I was able to have the whole row bottom border thick, but not just in the A to K range.

Sub Borders()
Dim cl As Range
Dim LC As Long
LC = Cells(1, Columns.Count).End(xlToLeft).Column
For Each cl In Range("$A$1:$A" & Cells(Rows.Count, "A").End(xlUp).Row)
If cl.Row Mod 10 = 0 Then
    With cl.Resize(1, LC).Borders(xlEdgeBottom)
      .LineStyle = xlDouble
      .Weight = xlThick
      .ColorIndex = xlAutomatic
    End With
End If
Next cl
End Sub

I added the option of resetting the count at the text code, if I'm getting help may as well get it done right first.

Answer 1

Having re-read your question I spotted the multiple sheets part.
Your code works fine and will do on multiple sheets with a couple of minor changes.

• This uses For....Each to loop through each sheet in the workbook that contains the code (ThisWorkbook).
• Select Case sht.Name is used so the code only runs on specified sheets. Move the code to the Do Nothing line if it's easier to list the sheets you don't want it to run on. Remove the Select...End Select if you want it to run on all sheets.
• With....End With is used to tell the range references what sheet you to look at - Cells, Rows, Columns - anything that is specific to a sheet is preceded by a . to tell the code you're referring to the sheet in the With line.

---

Sub Borders()
    Dim cl As Range
    Dim LC As Long
    Dim sht As Worksheet
    
    For Each sht In ThisWorkbook.Worksheets
        Select Case sht.Name
            Case "Sheet1", "Sheet2", "Sheet4"
                With sht
                    LC = .Cells(1, .Columns.Count).End(xlToLeft).Column
                    For Each cl In .Range("$A$1:$A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
                        If cl.Row Mod 9 = 0 Then
                            With cl.Resize(1, LC).Borders(xlEdgeBottom)
                              .LineStyle = xlSingle
                              .Weight = xlThick
                              .ColorIndex = xlAutomatic
                            End With
                        End If
                    Next cl
                End With
            Case Else
                'Do nothing.
        End Select
    Next sht
End Sub  

Further reading:
For...Each
With
Select Case