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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
brilliant thanks
0
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.