Insert Macro

[FusionRed]

Hello Everyone:

I'm a newbie to VBA and having trouble with what I think should be a very easy macro. I'm trying to search a range for all yellow lines beginning from E11 to the end of the data in the spreadsheet. I want to add a blank line above and below the yellow line and both lines should have no fill color.

The following is some code I've tried (just a version of many tries):

Sub Insert_Blank_Rows()
' Insert_Blank_Rows before and after total line in Elabor Budgets
' Macro recorded 1/19/2005 by Pitney Bowes Inc.
Dim Cell As Range
    Dim r As Range
        Set r = Range("E11", ActiveCell.SpecialCells(xlCellTypeLastCell))
For Each Cell In r
    Select Case Cell.Interior.ColorIndex
        Case Cell.Interior.ColorIndex = 36
            Selection.EntireRow.Insert
        End Select
        Next
End Sub

Can someone out there help me here????

Many thanks for your input and expertise.

FusionRed

[Zack Barresse]

Hi FusionRed! Welcome to VBAX!


I enclosed your code with our [ VBA ] tags (courtesy of Mark007!) to make your code look that way. I also moved the thread to the Excel Help forum.

Maybe you could do something like this ...

Sub Insert_Blank_Rows() 
' Insert_Blank_Rows before and after total line in Elabor Budgets
     ' Macro recorded 1/19/2005 by Pitney Bowes Inc.
Dim Cell As Range 
    Dim r As Range 
    Set r = Range("E11", Range("E65536").End(xlUp)) 
    For Each Cell In r 
        If Cell.Interior.ColorIndex = 36 Then
            Cell.Offset(1).Interior.ColorIndex = 0
            Cell.Offset(1).ClearContents
            Cell.Offset(-1).Interior.ColorIndex = 0
            Cell.Offset(-1).ClearContents
        End If
    Next 
End Sub

If I understood you correctly.
Let me know how it works.

[FusionRed]

Thanks for the help. Unfortunately, no lines are inserted when I run the code, in fact nothing changes on the spreadsheet.

So, I note your code doesn't use select row or insert commands. To help my understanding of VBA let me pose a question, when using the cell.offset(1) command does that automatically select the row above and do the insert???? And the ClearContents command is that done for good form?

Thanks again for all of your time and expertise.

FusionRed

[Zack Barresse]

Oh, sorry, I misunderstood. I thought you just wanted to clear the row of the cell above and below the cell with yellow highlight.

As far as the select goes, you don't need to Select anything really. The Offset will handle that as we iterate through the range. Try this ...

Option Explicit

Sub Insert_Blank_Rows()
' Insert_Blank_Rows before and after total line in Elabor Budgets
     ' Macro recorded 1/19/2005 by Pitney Bowes Inc.
Dim r As Range, i As Long
    Set r = Range("E11", Range("E65536").End(xlUp))
    For i = r.Cells.Count To 1 Step -1
        If r.Cells(i).Interior.ColorIndex = 36 Then
            r.Cells(i).EntireRow.Insert
            i = i + 1
            r.Cells(i).Offset(1).EntireRow.Insert
            r.Cells(i).Offset(1).EntireRow.Interior.ColorIndex = 0
        End If
    Next i
End Sub

[FusionRed]

Many thanks to everyone, this works like a charm.

FusionRed

[Zack Barresse]

Great! I'll mark this thread Solved then. You can do so on your own threads by going to Thread Tools --> Mark Solved --> Perform Action.

Take care!