macro to color items

[howard]

I have a macro which I have written to color A3:E3 with an accent style as well as where "Grand total" is found + 4 columns after Grand total is found


The macro colors A3:E3 + E4 and does not color the rows where"Grand Total is found" + 4 columns after this for eg if Grand Total is in A64, then A64:D64 to be colored using accent style

  Sub Colour_GrandTotal()
Sheets("pivot table").Select
With Range("A3:E3")
.Style = "Accent5"
End With



    Dim r As Range
    
    Set r = Sheets("Pivot Table").Cells.Find(What:="*Grand Total*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False)
    If Not r Is Nothing Then r.Resize(, 4).Style = "Accent5"
    Sheets("Pivot Table").Select
    
    
 End Sub

it would be appreciated if someone could assist me

 

[Scott T]

When you step though the code what does r equal? Sounds like it is not finding grand total.

 

[howard]

Thanks for your input

for some unknown reason, when I step into the macro and press F8 , I cannot see what is happening on my workbook


I have attached a link to dropbox showing the sample data

it would be appreciated if you could kindly amend my code

https://www.dropbox.com/s/26gzjw9ehc2l6y2/color Headers & Grand Total.xlsm?dl=0

 

[Scott T]

I can not download files at work.
As you step though the code after you run the line

Set r = Sheets("Pivot Table").Cells.Find(What:="*Grand Total*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False)

Over the curser over r to see the value or in the immediate window type

? r

If grand total is in you sheet you should see it in the string that is returned. If it is nothing then it did not find grand total.

 

[howard]

thanks for your input


the reason it was not working is the I had a formula search for "Grand"

I amended my macro to select Col A to search for Grand and it now works perfectly