Insert Blank Row after value change - Formating the blank row

[Clemm]

Hi all,

I'm new to using macro's but am slowly trying to figure them out as certain work tasks would be simpler and quicker if i could create basic macros.

I managed to find the macro on here to insert a blank row after value change which works fine but i would also like to change the colour of the inserted row so as to make it more obvious and readable. Below is the original macro found on here.

Sub InsertRowAtChangeInValue()
Dim lRow As Long
For lRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row To 2 Step -1
If Cells(lRow, "B") <> Cells(lRow - 1, "B") Then Rows(lRow).EntireRow.Insert
Next lRow
End Sub


I tried running my own macro to change the colour of the row and added into the original as below.

Sub InsertRowAtChangeInValue()
Dim lRow As Long
For lRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row To 2 Step -1
If Cells(lRow, "B") <> Cells(lRow - 1, "B") Then Rows(lRow).EntireRow.Insert
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Next lRow
End Sub

This now adds two extra blank rows and only colours the first cell in column A. There is obviously something wrong with the way i am inserting my macro into the original but can't figure out what. I'd be greatful for any suggestions.

Thanks,

Clemm

 

[VoG]

Try

Sub InsertRowAtChangeInValue()
Dim lRow As Long
For lRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row To 2 Step -1
    If Cells(lRow, "B") <> Cells(lRow - 1, "B") Then
        Rows(lRow).EntireRow.Insert
        Rows(lRow).Interior.ColorIndex = 3
    End If
Next lRow
End Sub

 

[Clemm]

Hi Vog,

That is excellent and worked. The original code was from you i copied off an earlier post

The sheet the data is on is in a workbook wih about 20 oher sheets (each named different as all reports are sent to us on one file). Would it be possible to add script to move the sheet into it's own workbook and to then save that workbook in a designated folder with save name being the same as the sheet name?

Thanks again,

Clemm