asked on

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

Saqib Husain

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

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jagwarman

ASKER

brilliant thanks

Saqib Husain

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.

Saqib Husain

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

Jagwarman

ASKER

Many thanks apologies for being a nuisance.

Jagwarman

ASKER

I don't believe it I am now getting

Object variable or With block variable not set

Saqib Husain

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

Jagwarman

ASKER

wow don't you sleep?

that's great that works

Many thanks