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
I have written code to do this, but fill colour is not cleared
Kindly amend my code
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