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

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

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
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

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