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

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

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

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

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


.

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


.

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


.



.

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


.



.