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
|

Copying formats?
 

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

Copying formats?
 

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

Copying formats?
 

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" wrote in message
ft.com...
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