Fomratting Subtotals Rows

[SAMCRO2014]

I am trying to format subtotal rows ( which are in column f) with a loop and I am doing something wrong. I want the macro to outline and fill in colour for the subtotal row (Columns A to P):

''Define last row in worksheet using column F
Dim Previous As Worksheet
Set Previous = ThisWorkbook.Sheets("Previous")
Dim LastRow As Long

LastRow = Previous.Cells(Rows.Count, 6).End(xlUp).Row

'Format all subtotals lines

For x = LastRow To 2 Step -1

If Cells(x, 6).Value Like "Total" Then
Range(x, "A:P").Select
Application.CutCopyMode = False
With Selection.Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 16764159
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If

Next x
End Sub


Can you see where I am going a miss?

 

[Norie]

Are you looking for the value 'Total' or for values that contain the word 'Total'?

If it's the latter try this.

Dim Previous As Worksheet
Dim LastRow As Long
Dim X As Long

    Set Previous = ThisWorkbook.Sheets("Previous")

    'Define last row in worksheet using column F

    LastRow = Previous.Cells(Rows.Count, 6).End(xlUp).Row

    'Format all subtotals lines

    For X = LastRow To 2 Step -1

        If Previous.Cells(X, 6).Value Like "*Total*" Then
        
            With Previous.Range("A" & X).Resize(, 16)

                With .Font
                    .Name = "Calibri"
                    .FontStyle = "Bold"
                    .Size = 11
                    .Strikethrough = False
                    .Superscript = False
                    .Subscript = False
                    .OutlineFont = False
                    .Shadow = False
                    .Underline = xlUnderlineStyleNone
                    .ThemeColor = xlThemeColorLight1
                    .TintAndShade = 0
                    .ThemeFont = xlThemeFontMinor
                End With
                .Borders(xlDiagonalDown).LineStyle = xlNone
                .Borders(xlDiagonalUp).LineStyle = xlNone
                With .Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With .Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With .Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With .Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With .Borders(xlInsideVertical)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                .Borders(xlInsideHorizontal).LineStyle = xlNone
                With .Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 16764159
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
            End With
        End If

    Next X
    
End Sub

 

[SAMCRO2014]

I was looking for the latter and it worked!!!! Thank you so much for your help!