row striping with VBA
in [Programming]
|
Prev: Alphabetize Macros in a module
Next: sendkeys
From: JLGWhiz on 12 May 2010 11:10 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" <Jock(a)discussions.microsoft.com> wrote in message news:591D812B-AA94-40B9-89C2-1784E9A9BF99(a)microsoft.com... > 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" <Jock(a)discussions.microsoft.com> wrote in message >> news:56B04CBD-B9D8-4C93-BAF5-C2FD37BDF5E6(a)microsoft.com... >> >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 >> >> >> . >>
From: Jock on 13 May 2010 11:24 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" <Jock(a)discussions.microsoft.com> wrote in message > news:591D812B-AA94-40B9-89C2-1784E9A9BF99(a)microsoft.com... > > 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" <Jock(a)discussions.microsoft.com> wrote in message > >> news:56B04CBD-B9D8-4C93-BAF5-C2FD37BDF5E6(a)microsoft.com... > >> >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 > >> > >> > >> . > >> > > > . >
From: Jim Cone on 13 May 2010 16:07
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 ) "Jock" <Jock(a)discussions.microsoft.com> wrote in message news:793DBB13-D378-4889-9DF9-7FA91DDD452A(a)microsoft.com... 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" <Jock(a)discussions.microsoft.com> wrote in message > news:591D812B-AA94-40B9-89C2-1784E9A9BF99(a)microsoft.com... > > 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" <Jock(a)discussions.microsoft.com> wrote in message > >> news:56B04CBD-B9D8-4C93-BAF5-C2FD37BDF5E6(a)microsoft.com... > >> >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 > >> > >> > >> . > >> > > > . > |