Macro, Excel: insert cell between colored cells ??

[JVLennox]

Hi everybody!

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

--> Each time I have a blue colored cell followed by a red colored cell 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, which determines the color. How do I get Excel to insert the cells throughout 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 With

[Tom Ogilvy]

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.



--
Regards,
Tom Ogilvy





"JVLennox" wrote:

>
> Hi everybody!
>
> I am trying to automatically insert an empty cell between colored
> cells!
>
> --> Each time I have a blue colored cell followed by a red colored cell
> 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, which
> determines the color. How do I get Excel to insert the cells throughout
> 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 With
>
>
> --
> JVLennox
> ------------------------------------------------------------------------
> JVLennox's Profile: http://www.excelforum.com/member.php...o&userid=32505
> View this thread: http://www.excelforum.com/showthread...hreadid=523180
>
>

[Nigel]

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

--
Cheers
Nigel



"JVLennox" <[email protected]> wrote in
message news:[email protected]...
>
> Hi everybody!
>
> I am trying to automatically insert an empty cell between colored
> cells!
>
> --> Each time I have a blue colored cell followed by a red colored cell
> 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, which
> determines the color. How do I get Excel to insert the cells throughout
> 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 With
>
>
> --
> JVLennox
> ------------------------------------------------------------------------
> JVLennox's Profile:
http://www.excelforum.com/member.php...o&userid=32505
> View this thread: http://www.excelforum.com/showthread...hreadid=523180
>

[JVLennox]

Hey!!

Thats GREAT!!!

Thank you so much!!!