row striping with VBA

[Jock]

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?

 

[Jeff]

ASAP Utilities has a procedure to do this (have a look under the Columns + Rows Menu.

 

[JLGWhiz]

See "Shading Alternate Rows in an Excel Worksheet" in the Excel help menu.

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.CF.html#rows

 

[Jock]

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.

 

[JLGWhiz]

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]

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

 

[Jim Cone]

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/ShadeDataRowsRelease151Trial.zip?a=7itg7e1y64Y )




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