Copying formats?

[Thief_]

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

 

[keepITcool]

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 :

 

[keepITcool]

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 :

 

[Tom Ogilvy]

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