Macro to insert all borders on all sheets

[howard]

I have written code to insert borders on all sheets from row 9 up to the row before where total appears in Col A (could be total repairs, total expenses etc)


I need the borders to be from Col a to the last Column Containing Text in Row 9.


Your assistance is much appreciated

 Sub Borders_All_Sheets()

Application.ScreenUpdating = False
Dim lngLstCol As Long, lngLstRow As Long

lngLstRow = ActiveSheet.UsedRange.Rows.Count
lngLstCol = ActiveSheet.UsedRange.Columns.Count

For Each rngCell In Range("A9:A" & lngLstRow)
    If rngCell.Value > "" Then
        r = rngCell.Row
        c = rngCell.Column
        Range(Cells(r, c), Cells(r, lngLstCol)).Select
            With Selection.Borders
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
    End If
Next

Application.ScreenUpdating = True

End Sub

 

[RCBricker]

not sure why you would want borders outside your used range,

dim ws as worksheet

for each ws in activeworkbook.worksheets
    ws.Cells.Select
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
next ws

 

[Zenwood]

Try,

Sub Macro1()
Cells.Borders.Color = RGB(200, 200, 200)
End Sub

 

[howard]

Thanks for the help guys

 

[MARK858]

I appear to be reading these requests a bit different

from row 9 up to the row before where total appears in Col A (could be total repairs, total expenses etc

I need the borders to be from Col a to the last Column Containing Text in Row 9

Sub FindTotal()
    Dim ws As Worksheet, FindTot As Range, myCol As Long, FindRW As Long
    
    For Each ws In ActiveWorkbook.Worksheets
    
        If WorksheetFunction.CountA(ws.Cells) <> 0 Then

            Set FindTot = ws.Columns(1).Find("total*", , xlValues, , xlByRows, xlNext, False)

            If Not FindTot Is Nothing Then
                myCol = ws.Rows(9).Find("*", , xlValues, , xlByColumns, xlPrevious).Column
                FindRW = FindTot.Row - 1
                ws.Range(ws.Cells(9, 1), ws.Cells(FindRW, myCol)).Borders.LineStyle = xlContinuous
            End If
            
        End If
        
    Next
End Sub

Btw, I have interpreted

from row 9 up to the row before where total appears in Col A

to mean

from row 9 down to the row before where total appears in Col A

 

[howard]

Thanks for the help. Your interpretation is 100%


Your code works perfectly