VBA simple step

[Mykasd]

Hello everyone,
I am trying to highlight every other row in a spreadsheet of contacts automatically using macros. The number of contacts change every time I run this program so I cannot do it in a set way. Can someone please help! Here is the coding I have so far:

Range("A2").Select
ContactNum = Cells(Rows.Count, 1).End(xlDown).Row

Range("A1").Select
For i = 1 To ContactNum
If i / 2 Is {need help here!} Then
Next i
Else
With Range("A2")
.Offset(1, 0).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
.PatternTintAndShade = 0
End With
End With
End If
Next i

THANK YOU!

[Bob Phillips]

Range("A2").Select
ContactNum = Cells(Rows.Count, 1).End(xlDown).Row

For i = 2 To ContactNum Step 2
With Cells(i, "A").Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
.PatternTintAndShade = 0
End With
Next i

[Mykasd]

The column count also changes and I am trying to highlight the entire row (every other row in a contact data base). Some columns are empty so you cannot do a xlToRight selection. Here is the coding i have to count the columns, but i don't know how to highlight the entire row:

Range("A1").Select
ColCount = Cells(Cols.Count, 1).End(xlToRight).Col

[Mykasd]

The column count also changes and I am trying to highlight the entire row (every other row in a contact data base). Some columns are empty so you cannot do a xlToRight selection. Here is the coding i have to count the columns, but i don't know how to highlight the entire row:

Range("A1").Select
ColCount = Cells(Rows.Count, 1).End(xlToRight).Row

[Bob Phillips]

Range("A2").Select
ContactNum = Cells(Rows.Count, 1).End(xlDown).Row

For i = 2 To ContactNum Step 2
With Rows(i).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
.PatternTintAndShade = 0
End With
Next i

[Mykasd]

My row count is counting all the rows in the spreadsheet, when in reality i may only have 15 or 20 contacts:

Range("A2").Select
ContactNum = Cells(Rows.Count, 1).End(xlDown).Row

Is my script correct?

[Mykasd]

To xld:

The only problem with this script:
Range("A2").Select
ContactNum = Cells(Rows.Count, 1).End(xlDown).Row

For i = 2 To ContactNum Step 2
With Rows(i).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
.PatternTintAndShade = 0
End With
Next i

Is that it highlights the entire row and for asthetical reasons, I would like it to end at column number ColumnCount which changes depending on the number of columns in the sheet.

[lucas]

Alt method...uses find and I didn't change the formatting to match yours. You will need to add a with/end with to deal with your formatting...
Dim LastRow As Long
Dim LastCol As Long
Dim i As Long

LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

LastCol = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

For i = 1 To LastRow Step 2
Range(Cells(i, 1), Cells(i, LastCol)).Interior.ColorIndex = 4
Next i

[Norie]

Why xlDown when you are starting at the bottom in the first place?

[malik641]

As an alternative, you can use Conditional Formatting. Something like:

Formula Is: =AND(ROW()=EVEN(ROW()),$A1<>"")

Just select the block of cells to be used and it will automatically highlight even rows when you put any value in column A.



Why xlDown when you are starting at the bottom in the first place? My thoughts exactly.

[mdmackillop]

The drawback with conditional formatting is that you can no longer highlight individual cells using other colours.

[malik641]

The drawback with conditional formatting is that you can no longer highlight individual cells using other colours. Didn't think of that. Thanks Malcolm.