find text insert row and colour row

Can an expert assist me with this please.

I need to find the last occurrence of the word Past in column AQ then insert a blank row beneath the last occurrence and then colour the row from A:AQ Yellow.

thanks in advance
JagwarmanAsked:
Who is Participating?
 
Saqib Husain, SyedEngineerCommented:
Or better

With Range("AQ:AQ").Find("Past", Range("AQ" & Rows.Count), , xlWhole, , xlPrevious).EntireRow
    .Offset(1).Insert
    .Offset(1).Resize(, 43).Interior.Color = RGB(255, 255, 0)
End With
0
 
Saqib Husain, SyedEngineerCommented:
Range("AQ:AQ").Find("Past", Range("AQ" & Rows.Count), , xlWhole, , xlPrevious).Offset(1).EntireRow.Insert
Range("AQ:AQ").Find("Past", Range("AQ" & Rows.Count), , xlWhole, , xlPrevious).Offset(1).EntireRow.Resize(, 43).Interior.Color = RGB(255, 255, 0)


********Edited******
0
 
JagwarmanAuthor Commented:
brilliant thanks
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Saqib Husain, SyedEngineerCommented:
From the OP in private mail
Unfortunately I never gave it a thought that if the Macro does not find "Past" it would fall over which it does. Can you help me out. I would be happy to repost so that you can get the point if you want me to.
0
 
Saqib Husain, SyedEngineerCommented:
set rngpast = Range("AQ:AQ").Find("Past", Range("AQ" & Rows.Count), , xlWhole, , xlPrevious).EntireRow
if not rngpast is nothing then
    rngpast.Offset(1).Insert
    rngpast.Offset(1).Resize(, 43).Interior.Color = RGB(255, 255, 0)
End if
0
 
JagwarmanAuthor Commented:
Many thanks apologies for being a nuisance.
0
 
JagwarmanAuthor Commented:
I don't believe it I am now getting

Object variable or With block variable not set
0
 
Saqib Husain, SyedEngineerCommented:
Try

set rngpast = Range("AQ:AQ").Find("Past", Range("AQ" & Rows.Count), , xlWhole, , xlPrevious)
if not rngpast is nothing then
    set rngpast=rngpast.EntireRow
    rngpast.Offset(1).Insert
    rngpast.Offset(1).Resize(, 43).Interior.Color = RGB(255, 255, 0)
End if
0
 
JagwarmanAuthor Commented:
wow don't you sleep?

that's great that works

Many thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.