Insert alternate blank rows filled a certain colour from A3:G6211

[Ironman]

Hi

I have a sheet containing data from A2 to G6210.

I need to insert a blank, shaded row between every row up to and including Col G (all of which contain data, which must not be amended).

Specifically, I would be very grateful for a module that I can run that will insert blank rows every other row, starting A3:A6211 inclusive, with the cells filled #EEE7D7 up to and including Col G.

i.e.

A3:G3 filled #EEE7D7

A5:G5 filled #EEE7D7

A7:G7 filled #EEE7D7

etc. etc.

A6211:G6211 filled #EEE7D7

Many thanks!

 

[johnnyL]

So do you want to insert a shaded blank row or a shaded row that has '#EEE7D7' in each cell for columns A through G?

 

[Ironman]

Hi Johnny - a shaded blank row would be great please (the RGB shade I need is 229 222 207). And I've just discovered that the hex is actually #E5DECF.

 

[johnnyL]

Is the following similar to what you are seeking?

Book1
	A	B	C	D	E	F	G	H
1	Header
2	2
3
4	3
5
6	4
7
8	5
9
10	6
11
12	7
13
14	8
15
16	9
17
18	10
19
Sheet6

 

[Ironman]

Yes, that's exactly it thanks Johnny.

 

[johnnyL]

Ok, It's probably not the most eloquent way to do it, but this is my first attempt ...

Sub InsertBlankShadedRows()
'
    Dim i               As Long
    Dim LastRowInSheet  As Long
'
    LastRowInSheet = Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
'
    Range("A2:A" & LastRowInSheet).Select
'
    For i = 1 To Range("A2:A" & LastRowInSheet).EntireRow.Count
        ActiveCell.Offset(1, 0).EntireRow.Insert
        ActiveCell.Resize(1, 7).Offset(1, 0).Interior.Color = RGB(229, 222, 207)
        ActiveCell.Offset(2, 0).Select
    Next
End Sub

 

[Ironman]

Many thanks Johnny - it's working and currently running row by row, it's taking quite a while (about 25% of the way through since I ran it, as soon as you posted).

 

[johnnyL]

Took about 1 minute to run on my computer for 6210 original rows of data.

 

[johnnyL]

Do you have more rows than the 6210 that you originally mentioned?

 

[Ironman]

Wow! It's just finished - and it's perfect, thanks ever so much Johnny! (As I have a fast processor I can only think it's because there's a fair bit of data in the filled rows?)

No, there aren't any additional rows beyond 6210.