Copying conditionally formatted cells

[SRMPURCHASE]

I consistently use the VBA below to insert a blank row above a row of data and copy it up to the newly inserted blank row, then edit a cell or two in the new data row and save the file.

Sub COPYROWINFOUP()

With Selection.EntireRow
.Copy
.Insert
End With
Application.CutCopyMode = False
End Sub

How do I include the newly created row into the conditional formatting "Applies to" field?

Before Data Entry
=$F$21:$F$3500

After Data Entry:
=$F$21:$F$607,$F$1160:$F$1664,$F$609:$F$612,$F$1890:$F$3507,$F$674:$F$923,$F$925:$F$1158,$F$1666:$F$1888,$F$614:$F$672

Currently every time I copy row info up it creates another conditional format for that cell only and additionally adds it to the current "Applies to", eventually causing a conditional format error and crashes Excel.

What do I need to do to not create a separate condition format for that cell and change the "Applies To" to the jumble noted above?

 

[handysmurf]

this is a big issue within Excel as far as I know... I have like 12 different rules and I have to go manually weed out and fix them regularly. Now I have had boat loads of them that took me forever to delete dups but I have not had it crash excel.

 

[Fluff]

Try

Sub COPYROWINFOUP()

With Selection.EntireRow
   .Offset(1).Insert
   .Resize(2).Filldown
End With
End Sub

 

[SRMPURCHASE]

Nice solution, works just fine, appreciate it.

 

[Fluff]

You're welcome & thanks for the feedback

 

[handysmurf]

is there a way to do conditional formatting with VBA so you don't have to constantly weed out and edit them?

 

[Fluff]

That depends on a lot of things.
It's better if you start a thread of your own, giving full details