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

afterdinnerspeaker

Board Regular
Joined
Jan 10, 2019
Messages
70
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

Well-known Member
Joined
Sep 21, 2016
Messages
2,735
VBA Code:
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
 

Forum statistics

Threads
1,085,181
Messages
5,382,167
Members
401,779
Latest member
Thonor

Some videos you may like

This Week's Hot Topics

Top