Macro, Excel: insert cell between colored cells ??

  • Thread starter Thread starter JVLennox
  • Start date Start date
J

JVLennox

Hi everybody!

I am trying to automatically insert an empty cell between colore
cells!

--> Each time I have a blue colored cell followed by a red colored cel
in column A, I want an empty cell to be inserted between these cells.

I tried to work something out, but all I can offer is this, whic
determines the color. How do I get Excel to insert the cells throughou
the whole column A???

THANKS FOR ANY HELP!!!!

JVLennox




Range("????").Select
With Selection.Interior
.ColorIndex = 5
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("????").Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End Wit
 
Sub efg()
Dim lastrow As Long, firstrow As Long
Dim i As Long, c as Long
lastrow = 100
firstrow = 5
c = 1
For i = lastrow To firstrow Step -1
If Cells(i, c).Interior.ColorIndex = 5 And _
Cells(i + 1, c).Interior.ColorIndex = 3 Then
Cells(i + 1, c).Insert Shift:=xlShiftDown
Cells(i + 1, c).Clear
End If
Next i
End Sub

worked for me. Set the values of lastrow and first row. change the value
of "c" to reflect the column you want to work on.
 
Does whole of column A as required, change the Rows.Count to the last row if
less than whole column to be changed

Sub Inserter()
Dim xr As Long
With Sheets(1)
For xr = 2 To Rows.Count '<< change this to extent of rows
If .Cells(xr, 1).Interior.ColorIndex = 3 And .Cells(xr - 1,
1).Interior.ColorIndex = 5 Then
.Rows(xr).EntireRow.Insert shift:=xlDown
.Cells(xr, 1).Interior.ColorIndex = xlNone
End If
Next xr
End With
End Sub
 
Back
Top