Identify even (or odd) rows without toolpak and 'ISEVEN'/'ISODD' ?
 

Hi,

I intend to have every even or odd row in another color, using
=iseven(row()) as conditionformat. However, i've learned that ISEVEN
demand the add-in Analysis Toolpak and I don't want that (of different
reasons).

Therefore - can someone find out a way to identify even or odd rows
without iseven/isodd function?

I can't have supporting cells on columns to the right becauser users
can add rows and if so, i can't build it on referring to other cells.

Happy to all suggestions,
Regards
tskogstrom

Identify even (or odd) rows without toolpak and 'ISEVEN'/'ISODD' ?
 

Bob Phillips offered this website for a similar question.
http://www.xldynamic.com/source/xld.CF.html#rows


Also, Carlo offered this solution in the same thread.
This colors the uneven rows.

Sub test()

For Each row_In Selection.Rows
If row_.Row() Mod 2 = 1 Then
row.Interior.ColorIndex = 35
End If
Next row_

End Sub

--
Pops Jackson


"tskogstrom" wrote:

Hi,

I intend to have every even or odd row in another color, using
=iseven(row()) as conditionformat. However, i've learned that ISEVEN
demand the add-in Analysis Toolpak and I don't want that (of different
reasons).

Therefore - can someone find out a way to identify even or odd rows
without iseven/isodd function?

I can't have supporting cells on columns to the right becauser users
can add rows and if so, i can't build it on referring to other cells.

Happy to all suggestions,
Regards
tskogstrom

Identify even (or odd) rows without toolpak and 'ISEVEN'/'ISODD' ?
 

Hi tskogstrom

Try http://www.cpearson.com/excel/banding.htm

or below, please

Option Explicit

'----------------------------------------------------------
' Procedure : mark2row
' Date : 20031002
' Author : Joergen Bondesen
' Purpose : Highlight every 2end row.
' Note : Avoid hidden row(s)
'----------------------------------------------------------
'
Sub mark2row()
Dim lastrow As Long
Dim cell As Variant
Dim Counter As Long

'// Macro
DeleteLastEmptyRows

lastrow = ActiveSheet.UsedRange.Rows _
(ActiveSheet.UsedRange.Rows.Count).Row

Range("A1:A" & lastrow).EntireRow.Interior.ColorIndex = _
xlColorIndexNone

For Each cell In Range("A1:A" & lastrow)

If Not cell.Rows.Hidden = True Then

Counter = Counter + 1

If Counter Mod 2 = 0 Then
'// Yellow
Range(cell.Address).EntireRow.Interior _
.ColorIndex = 6
End If
End If
Next cell

End Sub


Sub DeleteLastEmptyRows()
Dim lastrow As Long
Dim r As Long
Dim xx As Long

lastrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

Application.ScreenUpdating = False

For r = lastrow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
If Application.CountA(Rows(r)) < 0 Then Exit Sub
Next r

xx = ActiveSheet.UsedRange.Rows.Count
End Sub


--
Best regards
Joergen Bondesen


"tskogstrom" wrote in message
oups.com...
Hi,

I intend to have every even or odd row in another color, using
=iseven(row()) as conditionformat. However, i've learned that ISEVEN
demand the add-in Analysis Toolpak and I don't want that (of different
reasons).

Therefore - can someone find out a way to identify even or odd rows
without iseven/isodd function?

I can't have supporting cells on columns to the right becauser users
can add rows and if so, i can't build it on referring to other cells.

Happy to all suggestions,
Regards
tskogstrom

Identify even (or odd) rows without toolpak and 'ISEVEN'/'ISODD' ?
 

Hi,

Thank you both for your efforts, but I found out I can use MOD
function. Formatcondition used:
IF(MOD(RIGHT(TRUNC(A1,0)),2),MOD(RIGHT(TRUNC(A1,0) ),2*)
)=1

Kind regards
tskogstrom



Joergen Bondesen skrev:

Hi tskogstrom

Try http://www.cpearson.com/excel/banding.htm

or below, please

Option Explicit

'----------------------------------------------------------
' Procedure : mark2row
' Date : 20031002
' Author : Joergen Bondesen
' Purpose : Highlight every 2end row.
' Note : Avoid hidden row(s)
'----------------------------------------------------------
'
Sub mark2row()
Dim lastrow As Long
Dim cell As Variant
Dim Counter As Long

'// Macro
DeleteLastEmptyRows

lastrow = ActiveSheet.UsedRange.Rows _
(ActiveSheet.UsedRange.Rows.Count).Row

Range("A1:A" & lastrow).EntireRow.Interior.ColorIndex = _
xlColorIndexNone

For Each cell In Range("A1:A" & lastrow)

If Not cell.Rows.Hidden = True Then

Counter = Counter + 1

If Counter Mod 2 = 0 Then
'// Yellow
Range(cell.Address).EntireRow.Interior _
.ColorIndex = 6
End If
End If
Next cell

End Sub


Sub DeleteLastEmptyRows()
Dim lastrow As Long
Dim r As Long
Dim xx As Long

lastrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

Application.ScreenUpdating = False

For r = lastrow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
If Application.CountA(Rows(r)) < 0 Then Exit Sub
Next r

xx = ActiveSheet.UsedRange.Rows.Count
End Sub


--
Best regards
Joergen Bondesen


"tskogstrom" wrote in message
oups.com...
Hi,

I intend to have every even or odd row in another color, using
=iseven(row()) as conditionformat. However, i've learned that ISEVEN
demand the add-in Analysis Toolpak and I don't want that (of different
reasons).

Therefore - can someone find out a way to identify even or odd rows
without iseven/isodd function?

I can't have supporting cells on columns to the right becauser users
can add rows and if so, i can't build it on referring to other cells.

Happy to all suggestions,
Regards
tskogstrom