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]

Are the numbers actually numbers or Text appearing as numbers ??

 

[afterdinnerspeaker]

Thank you so much Michael! I highlighted the cells and they say "General" instead of "Number".

Jerry

 

[Michael M]

Looks like imported data ??
There is an apostrophe in front of all the numbers ??

 

[Michael M]

Maybe this

Sub 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).Insert
    End If
    If Range("B" & r).Value = "REVENUE" Or Range("B" & r).Value = "EXPENSE" Then
        With Range("B" & r).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 5296274
        End With
    End If
Next r
End Sub

 

[afterdinnerspeaker]

Thanks Michael! Sorry I didn't get back to you regarding the "imported" data question. The spreadsheet was imported from the Sage Simply Accounting program.

But, good news about your solution...everything works EXCEPT only half of the words "REVENUE" & "EXPENSE" get highlighted. I'm sure this is because these cells overflow to the adjacent cells.

Is there any way around this?

 

[Michael M]

USe

Sub 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).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]

OMG!! You did it so fast & with so little code! This amazes me...thank you so much for your help!

I want you to know how much I appreciate your time & effort and will do whatever I can to acknowledge it.

Cheers,

Jerry

 

[Michael M]

Glad to help...

 

[afterdinnerspeaker]

Hi again Michael!

So sorry to ask you another question with regard to the code you wrote for me.

Would it be possible to make another adjustment to the code to also BOLD each of the rows containing those 4-digit numbers?

Many thanks again,

Jerry