macro to clear fill colour

[howard]

I have a pivot table an need to clear the fill colour in Col D from 1 row below where Grand total appears in Col A


I have written code to do this, but fill colour is not cleared

Kindly amend my code

  Sub Clear_Colors()
  Dim rng As Range
Dim Lastrow As Long
Application.ScreenUpdating = False
    Sheets(1).Select
    Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
        For i = 4 To Lastrow
            Set rng = Range(Cells(i, 1), Cells(i, 4))
            If InStr(Cells(i, 1).Value, "Grand") Then
            With rng.Offset(1).Resize(15.5)
            
               With .Interior.Pattern = xlNone
                     End With
                      
                        End With
                    
                    
                    Else
                With rng
                    With .Interior
                        .Pattern = xlNone
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                    With .Font
                        .ColorIndex = xlAutomatic
                        .TintAndShade = 0
                    End With
                     With rng
                     .Font.Bold = False
                       End With
                 
                End With
            End If
        Next
Application.ScreenUpdating = True
End Sub

 

[SteveO59L]

have you used conditional formatting to colour the cells?

 

[RoryA]

Try something like this instead:

Sub Clear_Colors()
 Dim PT as pivottable
Dim Lastrow As Long

    set pt = Sheets(1).Pivottables(1)
    Lastrow = pt.tablerange1.row + pt.tablerange1.rows.count
    cells(lastrow, 1).resize(16).Interior.Pattern = xlNone

End Sub

I wasn't really sure what trying to resize the range to 15.5 rows was meant to do?

 

[howard]

No CF has been used

 

[howard]

Thanks for the help. The resize should have been 15,5

Thanks for the help Rory


I have amended your code

 cells(lastrow, 1).resize(16).Interior.Pattern = xlNone


To 

cells(lastrow, 1).resize(16,5).Interior.Pattern = xlNone

Code works perfectly