Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro, Excel: insert cell between colored cells ??
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 -- JVLenno ----------------------------------------------------------------------- JVLennox's Profile: http://www.excelforum.com/member.php...fo&userid=3250 View this thread: http://www.excelforum.com/showthread.php?threadid=52318 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro, Excel: insert cell between colored cells ??
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro, Excel: insert cell between colored cells ??
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro, Excel: insert cell between colored cells ??
Hey!! Thats GREAT!!! Thank you so much!! -- JVLenno ----------------------------------------------------------------------- JVLennox's Profile: http://www.excelforum.com/member.php...fo&userid=3250 View this thread: http://www.excelforum.com/showthread.php?threadid=52318 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 - cell borders not visible when cells colored | Excel Discussion (Misc queries) | |||
A macro that fishes for colored cells? | Excel Discussion (Misc queries) | |||
How to insert small colored dots or rectangles in cells of excel | Excel Discussion (Misc queries) | |||
Excel VBA not counting colored cells | Excel Programming | |||
Cell right next to colored cells is automatically colored on entering a value | Excel Programming |