i forgot:

you'll need a handler for your sheet to keep your shading,
following will work on row insert/delete or typing beyond usedrange.
but will not trigger on an edit/ cut or paste inside.

Private Sub Worksheet_Change(ByVal Target As Range)
Static sAddr$
If Me.UsedRange.Address <> sAddr Then
Shade
sAddr = Me.UsedRange.Address
End If

End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


keepITcool wrote :

>
> I think it would be a lot easier to get rid of the formatconditions
> and make a sub to do the alternate shading
> Sub Shade()
> Dim r
> With ActiveSheet.UsedRange
> .Interior.ColorIndex = xlNone
> For Each r In .Rows
> If r.Row Mod 2 = 1 Then r.Interior.ColorIndex = 36
> Next
> End With
> End Sub
>
>
>
> --
> keepITcool
> > www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> Thief_ wrote :
>
> > I am using a Conditional Formula:
> >
> > =(ROW(C4)/2=INT(ROW(C4)/2))
> >
> > to shade each second row. Because I need to move the rows around, I
> > need them to "remember" their shade colours which were issued to
> > them via the CF above.
> >
> > Is there a Copy, PasteSpecial SolidFormatsOnly type of command?
> > I just want to replace the cells who have shading activated by the
> > CF above to permanently keep the shading no matter where they are
> > moved to.
> >
> > XL2003