macro loop

H

Helen

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

Bob Phillips

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

Jason Morin

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
 
B

Bob Phillips

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
 
J

Jason Morin

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
 
D

Dave Peterson

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
 
B

Bob Phillips

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

Guest

Helen said:
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?
Click to expand...

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
 
Top