macro loop
 

I have worksheets with several hundred rows and want to record a macro that
will highlight every other row, starting at row 2, until the end. I can
record a macro to highlight several rows but how can I repeat until the end
of the worksheet?

cLastRow = Cells(Rows.Count,"A").End(xlUp).Row
For i = 1 to cLastRow Step 2
cells((i,"A").Entirerow.ColorIndex = 35
Next i

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Helen" wrote in message
...
I have worksheets with several hundred rows and want to record a macro
that
will highlight every other row, starting at row 2, until the end. I can
record a macro to highlight several rows but how can I repeat until the
end
of the worksheet?

Try:

Sub ColorRows()

Dim iLastRow As Long
Dim colorRng As Range
Dim cell As Range

Application.ScreenUpdating = False
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set colorRng = Range("2:" & iLastRow)
For Each cell In colorRng
With cell
If .Row Mod 2 = 0 Then
.EntireRow.Interior.ColorIndex = 3 'Red
'Blue=5,Green=10,Yellow=6
End If
End With
Next
Application.ScreenUpdating = True
End Sub

---
To use this macro, press ALT+F11, go to Insert Module,
and paste in the code in the window. Go back to XL (ALT+Q)
and run the macro under Tools Macro Macros.

HTH
Jason
Atlanta, GA



-----Original Message-----
I have worksheets with several hundred rows and want to
record a macro that
will highlight every other row, starting at row 2, until
the end. I can
record a macro to highlight several rows but how can I
repeat until the end
of the worksheet?
.

Hi Jason,

Your code has an built-in redundancy, in that in goes through each cell in
the range and setting the colour of that whole row if it meets the test
criteria. But as you set your range to the whole rows, the loop iterates
through each cell in the range, A2, B2, C2, etc. Thus A2 is tested and the
row is coloured, B2 is tested and the same row is coloured, C2 is tested and
the same row is coloured, etc.

This can be circumvented by changing

Set colorRng = Range("2:" & iLastRow)

to

Set colorRng = Range("A2:A" & iLastRow)


Regards

Bob

"Jason Morin" wrote in message
...
Try:

Sub ColorRows()

Dim iLastRow As Long
Dim colorRng As Range
Dim cell As Range

Application.ScreenUpdating = False
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set colorRng = Range("2:" & iLastRow)
For Each cell In colorRng
With cell
If .Row Mod 2 = 0 Then
.EntireRow.Interior.ColorIndex = 3 'Red
'Blue=5,Green=10,Yellow=6
End If
End With
Next
Application.ScreenUpdating = True
End Sub

---
To use this macro, press ALT+F11, go to Insert Module,
and paste in the code in the window. Go back to XL (ALT+Q)
and run the macro under Tools Macro Macros.

HTH
Jason
Atlanta, GA



-----Original Message-----
I have worksheets with several hundred rows and want to
record a macro that
will highlight every other row, starting at row 2, until
the end. I can
record a macro to highlight several rows but how can I
repeat until the end
of the worksheet?
.

Yep, you're right, Bob. I started to think about that
after I posted and realized that I didn't have to loop
through every cell of every row, only those cells in col.
A. I had "row" on the brain.

Thanks.

Jason


-----Original Message-----
Hi Jason,

Your code has an built-in redundancy, in that in goes
through each cell in
the range and setting the colour of that whole row if it
meets the test
criteria. But as you set your range to the whole rows,
the loop iterates
through each cell in the range, A2, B2, C2, etc. Thus A2
is tested and the
row is coloured, B2 is tested and the same row is
coloured, C2 is tested and
the same row is coloured, etc.

This can be circumvented by changing

Set colorRng = Range("2:" & iLastRow)

to

Set colorRng = Range("A2:A" & iLastRow)


Regards

Bob

"Jason Morin" wrote
in message
...
Try:

Sub ColorRows()

Dim iLastRow As Long
Dim colorRng As Range
Dim cell As Range

Application.ScreenUpdating = False
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set colorRng = Range("2:" & iLastRow)
For Each cell In colorRng
With cell
If .Row Mod 2 = 0 Then
.EntireRow.Interior.ColorIndex = 3 'Red
'Blue=5,Green=10,Yellow=6
End If
End With
Next
Application.ScreenUpdating = True
End Sub

---
To use this macro, press ALT+F11, go to Insert
Module,
and paste in the code in the window. Go back to XL
(ALT+Q)
and run the macro under Tools Macro Macros.

HTH
Jason
Atlanta, GA



-----Original Message-----
I have worksheets with several hundred rows and want
to
record a macro that
will highlight every other row, starting at row 2,
until
the end. I can
record a macro to highlight several rows but how can I
repeat until the end
of the worksheet?
.



.

One simple change and you'd be ok, though:

Change:
For Each cell In colorRng
to
For Each cell In colorRng.Rows

Now even though your variable names (Cell) aren't very descriptive, it'll work.

(Cell would be a Row (if that makes sense???))

Mass changing Cell to MyRow (only for readability):

Option Explicit

Sub ColorRows()

Dim iLastRow As Long
Dim colorRng As Range
Dim myRow As Range

Application.ScreenUpdating = False
iLastRow = cells(Rows.Count, "A").End(xlUp).Row
Set colorRng = Range("2:" & iLastRow)
For Each myRow In colorRng.Rows
With myRow
If .Row Mod 2 = 0 Then
.EntireRow.Interior.ColorIndex = 3 'Red
'Blue=5,Green=10,Yellow=6
End If
End With
Next
Application.ScreenUpdating = True
End Sub






Jason Morin wrote:

Yep, you're right, Bob. I started to think about that
after I posted and realized that I didn't have to loop
through every cell of every row, only those cells in col.
A. I had "row" on the brain.

Thanks.

Jason

-----Original Message-----
Hi Jason,

Your code has an built-in redundancy, in that in goes
through each cell in
the range and setting the colour of that whole row if it
meets the test
criteria. But as you set your range to the whole rows,
the loop iterates
through each cell in the range, A2, B2, C2, etc. Thus A2
is tested and the
row is coloured, B2 is tested and the same row is
coloured, C2 is tested and
the same row is coloured, etc.

This can be circumvented by changing

Set colorRng = Range("2:" & iLastRow)

to

Set colorRng = Range("A2:A" & iLastRow)


Regards

Bob

"Jason Morin" wrote
in message
...
Try:

Sub ColorRows()

Dim iLastRow As Long
Dim colorRng As Range
Dim cell As Range

Application.ScreenUpdating = False
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set colorRng = Range("2:" & iLastRow)
For Each cell In colorRng
With cell
If .Row Mod 2 = 0 Then
.EntireRow.Interior.ColorIndex = 3 'Red
'Blue=5,Green=10,Yellow=6
End If
End With
Next
Application.ScreenUpdating = True
End Sub

---
To use this macro, press ALT+F11, go to Insert
Module,
and paste in the code in the window. Go back to XL
(ALT+Q)
and run the macro under Tools Macro Macros.

HTH
Jason
Atlanta, GA



-----Original Message-----
I have worksheets with several hundred rows and want
to
record a macro that
will highlight every other row, starting at row 2,
until
the end. I can
record a macro to highlight several rows but how can I
repeat until the end
of the worksheet?
.



.


--

Dave Peterson

If you are going to go this way, you should take out the EntireRow property,
as myRow is an entirerow

Sub ColorRows()

Dim iLastRow As Long
Dim colorRng As Range
Dim myRow As Range

Application.ScreenUpdating = False
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set colorRng = Range("2:" & iLastRow)
For Each myRow In colorRng.Rows
With myRow
If .Row Mod 2 = 0 Then
.Interior.ColorIndex = 3 'Red '<<<<<<<<<<<<<<<<
'Blue=5,Green=10,Yellow=6
End If
End With
Next
Application.ScreenUpdating = True
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave Peterson" wrote in message
...
One simple change and you'd be ok, though:

Change:
For Each cell In colorRng
to
For Each cell In colorRng.Rows

Now even though your variable names (Cell) aren't very descriptive, it'll
work.

(Cell would be a Row (if that makes sense???))

Mass changing Cell to MyRow (only for readability):

Option Explicit

Sub ColorRows()

Dim iLastRow As Long
Dim colorRng As Range
Dim myRow As Range

Application.ScreenUpdating = False
iLastRow = cells(Rows.Count, "A").End(xlUp).Row
Set colorRng = Range("2:" & iLastRow)
For Each myRow In colorRng.Rows
With myRow
If .Row Mod 2 = 0 Then
.EntireRow.Interior.ColorIndex = 3 'Red
'Blue=5,Green=10,Yellow=6
End If
End With
Next
Application.ScreenUpdating = True
End Sub






Jason Morin wrote:

Yep, you're right, Bob. I started to think about that
after I posted and realized that I didn't have to loop
through every cell of every row, only those cells in col.
A. I had "row" on the brain.

Thanks.

Jason

-----Original Message-----
Hi Jason,

Your code has an built-in redundancy, in that in goes
through each cell in
the range and setting the colour of that whole row if it
meets the test
criteria. But as you set your range to the whole rows,
the loop iterates
through each cell in the range, A2, B2, C2, etc. Thus A2
is tested and the
row is coloured, B2 is tested and the same row is
coloured, C2 is tested and
the same row is coloured, etc.

This can be circumvented by changing

Set colorRng = Range("2:" & iLastRow)

to

Set colorRng = Range("A2:A" & iLastRow)


Regards

Bob

"Jason Morin" wrote
in message
...
Try:

Sub ColorRows()

Dim iLastRow As Long
Dim colorRng As Range
Dim cell As Range

Application.ScreenUpdating = False
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set colorRng = Range("2:" & iLastRow)
For Each cell In colorRng
With cell
If .Row Mod 2 = 0 Then
.EntireRow.Interior.ColorIndex = 3 'Red
'Blue=5,Green=10,Yellow=6
End If
End With
Next
Application.ScreenUpdating = True
End Sub

---
To use this macro, press ALT+F11, go to Insert
Module,
and paste in the code in the window. Go back to XL
(ALT+Q)
and run the macro under Tools Macro Macros.

HTH
Jason
Atlanta, GA



-----Original Message-----
I have worksheets with several hundred rows and want
to
record a macro that
will highlight every other row, starting at row 2,
until
the end. I can
record a macro to highlight several rows but how can I
repeat until the end
of the worksheet?
.



.


--

Dave Peterson

Helen wrote:
I have worksheets with several hundred rows and want to record a macro that
will highlight every other row, starting at row 2, until the end. I can
record a macro to highlight several rows but how can I repeat until the end
of the worksheet?

To state more directly what the other guys have been talking about, you
can't really *record* a macro with a loop. What you can do is record
the inner loop of the macro, and then go back and manually edit the
macro to add the loop around that code.

Frequently I use a While/Wend loop for this purpose. I start the macro
with a cell selected and "While" that cell is greater than 0 I execute
the loop and move the selected cell down one row.

This works nicely to build a table for a chart for example. I create a
column of X values. The macro plugs them one at a time into the
spreadsheet, then fetches the result to the table and increments the
selected cell down one row.

Good luck...

Bill