Find certain "Row" (in Column B), then find last cell in same Row (in Column H) to reformat

[afterdinnerspeaker]

Hi Everyone!

I use the following lines of code to find words "TOTAL REVENUE" in Column "B", then insert one blank row above that row:

Set Fnd = Range("B:B").find("TOTAL REVENUE", , , xlWhole, , , True, , False)
If Not Fnd Is Nothing Then Fnd.EntireRow.Insert

PROBLEM: After finding that Row (and before inserting another row), I'd like to reformat the last "used" cell (containing a number) in that row which will be in Column "H"
Then, I want to reformat that cell by removing highlighting, bolding & font color leaving the figure in black with no formatting
Finally, I'd like to insert a blank row above the row containing "TOTAL REVENUE"

Hoping someone can help me,

Jerry

 

[footoo]

Set fnd = Range("B:B").Find("TOTAL REVENUE", , , xlWhole, , , True, , False)
If Not fnd Is Nothing Then
    With Cells(fnd.Row, "H")
        .Font.FontStyle = "Regular"
        .Font.ColorIndex = xlAutomatic
        .Interior.Pattern = xlNone
    End With
    fnd.EntireRow.Insert
End If

 

[afterdinnerspeaker]

Thank you footoo!

Bingo! Works like a charm!

Really appreciate your swift solution and hoping you have a nice weekend,

Jerry