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