Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
row striping with VBA
I have used the followinf CF to shade every other row a certain colour:
=MOD(ROW(),2)=0 When data is copied and pasted (xlPasteFormats) in to this sheet, this row striping is overwritten. Rather than tweak the copying code, I was wondering if there is a VBA method which will achieve the same result as the formula above and stripe alternate rows? -- Traa Dy Liooar Jock |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
row striping with VBA
ASAP Utilities has a procedure to do this (have a look under the Columns + Rows Menu.
--- frmsrcurl: http://msgroups.net/microsoft.public...iping-with-VBA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
row striping with VBA
See "Shading Alternate Rows in an Excel Worksheet" in the Excel help menu.
"Jock" wrote in message ... I have used the followinf CF to shade every other row a certain colour: =MOD(ROW(),2)=0 When data is copied and pasted (xlPasteFormats) in to this sheet, this row striping is overwritten. Rather than tweak the copying code, I was wondering if there is a VBA method which will achieve the same result as the formula above and stripe alternate rows? -- Traa Dy Liooar Jock |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
row striping with VBA
See http://www.xldynamic.com/source/xld.CF.html#rows
-- HTH Bob "Jock" wrote in message ... I have used the followinf CF to shade every other row a certain colour: =MOD(ROW(),2)=0 When data is copied and pasted (xlPasteFormats) in to this sheet, this row striping is overwritten. Rather than tweak the copying code, I was wondering if there is a VBA method which will achieve the same result as the formula above and stripe alternate rows? -- Traa Dy Liooar Jock |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
row striping with VBA
Thanks but these are all CF solutions and as such, can be deleted/removed. I
require something which cannot change regardless of whether the user applies a filter, sorts a selection or copies formatting from a different sheet. Any suggestions welcomed. -- Traa Dy Liooar Jock "JLGWhiz" wrote: See "Shading Alternate Rows in an Excel Worksheet" in the Excel help menu. "Jock" wrote in message ... I have used the followinf CF to shade every other row a certain colour: =MOD(ROW(),2)=0 When data is copied and pasted (xlPasteFormats) in to this sheet, this row striping is overwritten. Rather than tweak the copying code, I was wondering if there is a VBA method which will achieve the same result as the formula above and stripe alternate rows? -- Traa Dy Liooar Jock . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
row striping with VBA
I doen't make much difference what method is used to shade the rows. As
soon as a copy and paste action is taken, the format of the destination cell is wiped out.. The cell shading adds bytes to the file size without really adding any value. It is useful for printing large tables of numerical data so it will be easy to read the hard copy without jumping to the wrong line. But if it is only for aesthetic purposes, it can be more trouble than it is worth. "Jock" wrote in message ... Thanks but these are all CF solutions and as such, can be deleted/removed. I require something which cannot change regardless of whether the user applies a filter, sorts a selection or copies formatting from a different sheet. Any suggestions welcomed. -- Traa Dy Liooar Jock "JLGWhiz" wrote: See "Shading Alternate Rows in an Excel Worksheet" in the Excel help menu. "Jock" wrote in message ... I have used the followinf CF to shade every other row a certain colour: =MOD(ROW(),2)=0 When data is copied and pasted (xlPasteFormats) in to this sheet, this row striping is overwritten. Rather than tweak the copying code, I was wondering if there is a VBA method which will achieve the same result as the formula above and stripe alternate rows? -- Traa Dy Liooar Jock . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
row striping with VBA
I hear you.
However, its a big old sheet which will be on a public computer therefore I need row banding to help users. Using a command button, I was hoping to apply banding regardless of the formatting copied and pasted in. It fails at the Selection.FormatConditions.Add Type line: Private Sub CommandButton1_Click() Dim Sh As Worksheet 'source sheet Dim lngLastRow As Long Set Sh = ActiveWorkbook.ActiveSheet lngLastRow = Sh.Cells(Cells.Rows.Count, "A").End(xlUp).Row Range("A4:E" & lngLastRow).Activate 'Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(MOD(ROW(),2)=0" Selection.FormatConditions(1).Interior.ColorIndex = 24 End Sub -- Traa Dy Liooar Jock "JLGWhiz" wrote: I doen't make much difference what method is used to shade the rows. As soon as a copy and paste action is taken, the format of the destination cell is wiped out.. The cell shading adds bytes to the file size without really adding any value. It is useful for printing large tables of numerical data so it will be easy to read the hard copy without jumping to the wrong line. But if it is only for aesthetic purposes, it can be more trouble than it is worth. "Jock" wrote in message ... Thanks but these are all CF solutions and as such, can be deleted/removed. I require something which cannot change regardless of whether the user applies a filter, sorts a selection or copies formatting from a different sheet. Any suggestions welcomed. -- Traa Dy Liooar Jock "JLGWhiz" wrote: See "Shading Alternate Rows in an Excel Worksheet" in the Excel help menu. "Jock" wrote in message ... I have used the followinf CF to shade every other row a certain colour: =MOD(ROW(),2)=0 When data is copied and pasted (xlPasteFormats) in to this sheet, this row striping is overwritten. Rather than tweak the copying code, I was wondering if there is a VBA method which will achieve the same result as the formula above and stripe alternate rows? -- Traa Dy Liooar Jock . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
row striping with VBA
Remove the first parenthesis so it looks like... Formula1:="=MOD(ROW(),2)=0" Also, you might want to try out my commercial Excel add-in "Shade Data Rows". -- Jim Cone Portland, Oregon USA ("Shade Data Rows" - shade every N rows, shade row groups, shade by cell value... no registration, full version http://www.humyo.com/10358029/ShadeD...?a=7itg7e1y64Y ) "Jock" wrote in message ... I hear you. However, its a big old sheet which will be on a public computer therefore I need row banding to help users. Using a command button, I was hoping to apply banding regardless of the formatting copied and pasted in. It fails at the Selection.FormatConditions.Add Type line: Private Sub CommandButton1_Click() Dim Sh As Worksheet 'source sheet Dim lngLastRow As Long Set Sh = ActiveWorkbook.ActiveSheet lngLastRow = Sh.Cells(Cells.Rows.Count, "A").End(xlUp).Row Range("A4:E" & lngLastRow).Activate 'Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(MOD(ROW(),2)=0" Selection.FormatConditions(1).Interior.ColorIndex = 24 End Sub -- Traa Dy Liooar Jock "JLGWhiz" wrote: I doen't make much difference what method is used to shade the rows. As soon as a copy and paste action is taken, the format of the destination cell is wiped out.. The cell shading adds bytes to the file size without really adding any value. It is useful for printing large tables of numerical data so it will be easy to read the hard copy without jumping to the wrong line. But if it is only for aesthetic purposes, it can be more trouble than it is worth. "Jock" wrote in message ... Thanks but these are all CF solutions and as such, can be deleted/removed. I require something which cannot change regardless of whether the user applies a filter, sorts a selection or copies formatting from a different sheet. Any suggestions welcomed. -- Traa Dy Liooar Jock "JLGWhiz" wrote: See "Shading Alternate Rows in an Excel Worksheet" in the Excel help menu. "Jock" wrote in message ... I have used the followinf CF to shade every other row a certain colour: =MOD(ROW(),2)=0 When data is copied and pasted (xlPasteFormats) in to this sheet, this row striping is overwritten. Rather than tweak the copying code, I was wondering if there is a VBA method which will achieve the same result as the formula above and stripe alternate rows? -- Traa Dy Liooar Jock . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row Striping | Excel Discussion (Misc queries) |