Insert row above rows containing certain information

[afterdinnerspeaker]

I posted this to Excel Forum earlier today but unable to get Macro to work satisfactorily...please HELP!

Hi there folks!

Just wondering if anyone could help me with the following tasks:

• Insert one row above each row containing a “number” in column “C”
• Insert one row above each of the two rows containing headings “TOTAL REVENUE” and “TOTAL EXPENSE” in column “B”
• Highlight the two instances of the one word headings “REVENUE” and “EXPENSE” (currently but not always in row #3 and row #13 ) in “light green”
PLEASE NOTE: The words "REVENUE" & "EXPENSE" span both column B & C in their respective rows


I attached a sample of the spreadsheet.

Many thanks in advance,

Jerry
​

<fieldset class="postcontent"><legend>

Attached Files</legend>

• <!-- BEGIN TEMPLATE: postbit_attachment -->
• 
  SAMPLE BEFORE MACRO.xlsx‎ (14.2 KB, 3 views) Download

</fieldset>

 

[Michael M]

Try

S

ub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "B").End(xlUp).Row
For r = lr To 3 Step -1
    If Len(Range("C" & r)) = 4 Or Range("B" & r).Value = "TOTAL REVENUE" Or Range("B" & r).Value = "TOTAL EXPENSE" Then
        Rows(r).Font.Bold = True
        Rows(r).Insert
    End If
    If Range("B" & r).Value = "REVENUE" Or Range("B" & r).Value = "EXPENSE" Then
        With Range("B" & r & ":C" & r).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 5296274
        End With
    End If
Next r
End Sub

 

[afterdinnerspeaker]

Works perfectly...as usual!

Thank you so much again Michael for your expertise (and patience).

Regards from the other side of the world,

Jerry

 

[Michael M]

Pleasure...