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

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

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
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!!

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


This should effectively always colour your first row blue, regardless
if it's even or odd.


--
Excelibur
------------------------------------------------------------------------
Excelibur's Profile: http://www.excelforum.com/member.php...o&userid=29788
View this thread: http://www.excelforum.com/showthread...hreadid=494927

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

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