insert blank row

J

J.W. Aldridge

Want to insert blank row right before the first instance where a row
color is gray (color index 41).

Tried to work out the folliowing but no success.

Sub InsertRows()
Dim i As Long
i = 2
Do Until Trim(Cells(i, 1)) = ""
If cell.Interior.ColorIndex = 41 Then
Cells(i, 1).EntireRow.Insert
i = i + 2
Else
i = i + 1
End If
End Sub
 
T

Talorthain

Want to insert blank row right before the first instance where a row
color is gray (color index 41).

Tried to work out the folliowing but no success.

Sub InsertRows()
Dim i As Long
i = 2
Do Until Trim(Cells(i, 1)) = ""
If cell.Interior.ColorIndex = 41 Then
Cells(i, 1).EntireRow.Insert
i = i + 2
Else
i = i + 1
End If
End Sub
Click to expand...

try replacing cells(i,1).entirerow with

Rows(i:i).Select
Selection.Insert Shift:=xlDown


Regards
 
P

Per Jessen

Try this:

Sub InsertRows()
Dim i As Long
i = 2
Do Until Trim(Cells(i, 1)) = ""
If Cells(i, 1).Interior.ColorIndex = 41 Then
Cells(i, 1).EntireRow.Insert
i = i + 2
Else
i = i + 1
End If
Loop
End Sub

Regards,
Per
 
P

Per Jessen

You need a Loop statement to finish the Do / Loop loop, and in the
line:

If cell.Interior.ColorIndex .....

you have not Set any reference to the cell variable.

Best regards,
Per
 
Top