How to format a subtotal row

BillZab 

If the position of the subtotal rows varies each week and there could be different numbers of rows between each subtotal row, we can adjust the code to dynamically identify and format the subtotal row based on certain criteria. One way to do this is to check if a row contains the word "Total" in the employee name column. Here's how you can modify the code to achieve this:

Sub ApplySubtotalFormatting()
    ' Remove existing subtotal
    Range("A2").ListObject.Unlist
    
    ' Apply subtotal
    Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(6), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    
    ' Get the last row of data
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row
    Dim i As Long
    For i = 3 To lastRow
        ' Check if the row contains "Total" in the employee name column
        If InStr(1, Cells(i, 1).Value, "Total", vbTextCompare) > 0 Then
            ' Format the subtotal row
            Range("A" & i & ":F" & i).Font.Bold = True
            Range("A" & i & ":F" & i).Interior.Color = RGB(192, 192, 192) ' Change to your desired color
        End If
    Next i
End Sub

This code will loop through each row of the data and check if the employee name column contains the word "Total". If it does, it will format that row as a subtotal row. Adjust the range and color code to fit your specific requirements. This approach should work regardless of the position of the subtotal rows or the number of rows between them.