help with a sub
in [Programming]
|
Prev: Combine 2 spreadsheets by parsing from one to the other
Next: Error of "application-defined or object-defined error" in Goal
From: Jock on 13 May 2010 12:15 Hi, can anybody tell me why the following code fails at FormatConditions.Add Private Sub CommandButton1_Click() Dim Sh As Worksheet 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.Add Type:=xlExpression, Formula1:="=(MOD(ROW(),2)=0" Selection.FormatConditions(1).Interior.ColorIndex = 24 End Sub Thanks -- Traa Dy Liooar Jock
From: EricG on 13 May 2010 13:22 You have an extra open paren just before MOD: "=(MOD" should be "=MOD". HTH, Eric "Jock" wrote: > Hi, > can anybody tell me why the following code fails at FormatConditions.Add > > Private Sub CommandButton1_Click() > > Dim Sh As Worksheet > 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.Add Type:=xlExpression, > Formula1:="=(MOD(ROW(),2)=0" > Selection.FormatConditions(1).Interior.ColorIndex = 24 > End Sub > > Thanks > -- > Traa Dy Liooar > > Jock
From: Gord Dibben on 13 May 2010 19:24 See other replies for the error fix. If you want the banding to stick when sorting or filtering use this formula. =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 Gord Dibben MS Excel MVP On Thu, 13 May 2010 09:15:02 -0700, Jock <Jock(a)discussions.microsoft.com> wrote: >Hi, >can anybody tell me why the following code fails at FormatConditions.Add > >Private Sub CommandButton1_Click() > >Dim Sh As Worksheet >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.Add Type:=xlExpression, >Formula1:="=(MOD(ROW(),2)=0" > Selection.FormatConditions(1).Interior.ColorIndex = 24 >End Sub > >Thanks
From: Jock on 14 May 2010 05:28
Thanks for the heads up on the par. Didn't sort it tho. I still get "Object defined or Application defined error" at the same point. I recorded a macro whilst applying banding and adapted it to search for the last row which will change. I must be missing something obvious! -- Traa Dy Liooar Jock "EricG" wrote: > You have an extra open paren just before MOD: > > "=(MOD" should be "=MOD". > > HTH, > > Eric > > "Jock" wrote: > > > Hi, > > can anybody tell me why the following code fails at FormatConditions.Add > > > > Private Sub CommandButton1_Click() > > > > Dim Sh As Worksheet > > 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.Add Type:=xlExpression, > > Formula1:="=(MOD(ROW(),2)=0" > > Selection.FormatConditions(1).Interior.ColorIndex = 24 > > End Sub > > > > Thanks > > -- > > Traa Dy Liooar > > > > Jock |