Referencing other cells based on selection

I have a problem with referencing to other cells in my sheet. I have a VBA that creates tables out of a pivot table and saves them on different sheets. Example of the table:

The number of rows for any name can change, the total row position can therefore change too. I needed to bold the whole subtotal and total rows (in this case it would be B9:G9, B12:G12, B14:G14, A15:G15) so I tried this:

           If Right(cell.Value, 5) Like "Total" Then
                  With cell
                  .HorizontalAlignment = xlLeft
                  .Font.Bold = True
                    End With
                    End If
                Next cell

which naturally only bolded the one cell which has the 'Total' text in it. The problem is referencing to the other cells in the rows I want to bold - the merged cells are messing that up. When I tried this:

cell.EntireRow.Font.Bold = True

the whole Project got bold (A5:G14). I also tried the offset function, but offset(0,1) sent me to cell C5.

Do you please have any idea how to make the whole total and subtotal rows bold?

Thanks, B.

CodePudding user response：

You could do something like this:

Sub Tester()
    Dim ws As Worksheet, c As Range
    
    Set ws = ActiveSheet
    For Each c In ActiveSheet.UsedRange.Cells
        If c.Value Like "*Total" Then
            'from cell with "*Total" to last column
            With ws.Range(c, ws.Cells(c.Row, "G"))
                .HorizontalAlignment = xlLeft
                .Font.Bold = True
            End With
        End If
    Next c
End Sub