Copying formats?

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

--
|
+-- Julian
|

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

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

If he moved a shaded row to a position not normally shaded by the shade
routine, how would you see the handler/shade routine (as you have presented
it) preserving the shading? I think you would need a different type of
shade routine for dynamic updating after you ran the first one to replace
the shading performed by the conditional formatting. I would suggest
something like the below although it assumes column A would not be
disturbed.

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

End Sub

Sub Shade1()
Dim r As Range
Dim i As Long
With ActiveSheet.UsedRange
For Each r In .Rows
i = r.Cells(1, 1).Interior.ColorIndex
r.EntireRow.Interior.ColorIndex = xlNone
r.Interior.ColorIndex = i
Next
End With
End Sub

--
Regards,
Tom Ogilvy



"keepITcool" <[email protected]> wrote in message
news:[email protected]...
> 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