stop a looping macro after the last row of data

[still learning]

Hi I use the following macro to fill every other row with a color to make it easier to read the spreadsheat.

Sub filleveryotherrow()
Dim i As Long
For i = 1 To Range("times").Value
fillcolor
Next i
End Sub

This is the fillcolor macro

Sub fillcolor()
ActiveCell.Range("A1:l1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.COLOR = 15652797
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.Offset(2, 0).Range("A1").Select
End Sub

When I’m done adding new data to the rows (from A to L),
I put the cursor on the first cell in A where I want to start
Is there a way to have the first marco stop on or after the last row that has data?

Now I have to adjust the range “times” and guess at how many times



mike

 

[bebo021999]

Assum you want to paint each row in A1:L100, one after one

Sub color()
For Each cell In Range("A1:L100")
If cell.Row Mod 2 = 0 Then
    With cell.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .color = 15652797
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End If
Next
End Sub

Book3
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Sheet1

 

[6StringJazzer]

This could be done with simple conditional formatting. That way it is always updated automatically, you never have to run code. Highlight columns A:L and add this rule as a formula:

=ISEVEN(ROW(A1))

and set up desired fill color.

 

[Peter_SSs]

Is there a way to have the first marco stop on or after the last row that has data?

You could change the first macro to this ..

Sub filleveryotherrow()
  Do While ActiveCell.Row <= Columns("A:L").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    fillcolor
  Loop
End Sub

.. BUT
- That still requires you to remember to select A1 first (If you forget you will get some strange results). Or you could add a line to that first macro to select A1 before the loop starts.
- Selecting cells/ranges to work with them is almost never needed and slows your code considerably.

You could consider this single macro that will do the same shading to the desired number of odd rows like your original code but without the selecting.

Sub filleveryotherrow_v2()
  Dim i As Long
  
  For i = 1 To Columns("A:L").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Step 2
    With Rows(i).Resize(, 12).Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 15652797
      .TintAndShade = 0
      .PatternTintAndShade = 0
    End With
  Next i
End Sub

 

[still learning]

thanks all for the help
I like what Peter_SSs's code is.
It stops the filling in based on the last line. I think the sheet looks better when the filling in stops at the end of the data. (what needs to be filled)
When I add data to the sheet, I'll have a button on the ribbon that un-fills all the rows and start at A1 and fills till the last line.

I also like the better codes to fill than what i came up with , and the conditional format one.
As my name implies, I'm still learning. I have a workbook with all the codes and such that I've picked up here and go to it a lot when I'm trying to write code.
I'm very happy that i can go to this site to help me.

It would be nice to be able to mark more than one post with the best solution. Sometimes there are more "bests"

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

 

[still learning]

Hi,
Hope it's alright to ask another question about this
How would I start the fill in A2 in stead of A1 ??
I tried to change the >>("A:L)<< to A2:L, but I got an error
I may want to use this for another sheet down the road


mike

 

[Peter_SSs]

For i = 2 to ….

 

[still learning]

Thanks Pete
(another "best" answer)
mike

 

[Peter_SSs]

Cheers.