Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying formats - column widths, formats, outlining to worksheets | Excel Worksheet Functions | |||
Copying printer formats | Excel Discussion (Misc queries) | |||
copying conditon formats with $ | Excel Worksheet Functions | |||
Copying Formats | Excel Discussion (Misc queries) | |||
Copying Cell Formats | Excel Programming |