conditional formatting w/ check for beginning odd or even

[barberboy]

Greeting Excel programmers. I have a simple question.

I have two macros that i use to add a conditional format to highlight
every other row for the cells that are selected. One macro gives the
EVEN rows (e.g. 2,4,6) a blue background and ODD (e.g. 1,3,5) rows a
white background. The other does the opposite: ODD rows blue, EVEN rows
odd. (The only thing different about them is the '=' vs the '>' in the
conditional formatting function.)

What i would like is a macro that will check to see if the FIRST row in
the selection is odd, and if it is, apply the EvenRowsBlue macro. If it
is even, apply the OddRowsBlue macro.

These are the two macros i have now, but if there is a different/better
way without them, i am up for that.

Sub OddRowsBlue()
'
' Created by barberboy, 18-12-05

With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)>0"
.FormatConditions(1).Interior.ColorIndex = 34
.Interior.ColorIndex = 2
End With
End Sub

Sub EvenRowsBlue()
'
' Created by barberboy, 18-12-05

With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)=0"
.FormatConditions(1).Interior.ColorIndex = 34
.Interior.ColorIndex = 2
End With
End Sub

Thanks in advance!!

 

[Tom Ogilvy]

Sub WhatYouAskedfor()
If Selection(1).Row Mod 2 = 1 Then
'first row is odd
EvenRowBlue
Else
' first row is even
OddRowsBlue
End If
End Sub

A possible alternative:

Sub abcEven()
Dim even As Long, odd As Long
If Selection(1).Row Mod 2 = 1 Then
odd = 34
even = 2
Else
odd = 2
even = 34
End If
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)>0"
.FormatConditions(1).Interior.ColorIndex = even
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)=0"
.FormatConditions(2).Interior.ColorIndex = odd
End With
End Sub

 

[Excelibur]

Sub OddRowsBlue()
'
' Created by barberboy, 18-12-05

With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)>0"
.FormatConditions(1).Interior.ColorIndex = 34
.Interior.ColorIndex = 2
End With
End Sub

Sub EvenRowsBlue()
'
' Created by barberboy, 18-12-05

With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)=0"
.FormatConditions(1).Interior.ColorIndex = 34
.Interior.ColorIndex = 2
End With
End Sub

Code:
--------------------

Sub RowsBlue()
With Selection
.FormatConditions.Delete
If Selection.Cells(1,1).Row Mod 2 > 0 Then
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)>0"
Else
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)=0"
End If
.FormatConditions(1).Interior.ColorIndex = 34
.Interior.ColorIndex = 2
End With
End Sub

 

[barberboy]

Thanks Tom and Excelibur. Both abcEven and RowsBlue do EXACTLY what i
am needing. You guys are great!!

 

[barberboy]

Final Macro:

Sub BlueRows()
With Selection
.FormatConditions.Delete
If Selection.Cells(1, 1).Row Mod 2 = 0 Then
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)>0"
Else
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)=0"
End If
.FormatConditions(1).Interior.ColorIndex = 34
.Interior.ColorIndex = 2
End With
End Sub