On Feb 5, 10:46 am, "okrob" wrote:
would someone take a look at this and let me know if there is a
shorter method? I only want to change the color on every other row
from Column A to O...
I just modified a 'select every other row' code someone posted here.
This works, but I thought maybe it could be cleaned up a bit.
Rob

ok, here's the code...

Sub color()
Dim strCol As String, rowStart As Long, rowOffset As Long
Dim rg As Range
Dim rng As Range
Dim lastRow As Long, i As Long
Dim x As Integer
x = 1
For x = 1 To 15 Step 1
If x = 1 Then
strCol = "A"
Else
If x = 2 Then
strCol = "B"
Else
If x = 3 Then
strCol = "C"
Else
If x = 4 Then
strCol = "D"
Else
If x = 5 Then
strCol = "E"
Else
If x = 6 Then
strCol = "F"
Else
If x = 7 Then
strCol = "G"
Else
If x = 8 Then
strCol = "H"
Else
If x = 9 Then
strCol = "I"
Else
If x = 10 Then
strCol = "J"
Else
If x = 11 Then
strCol = "K"
Else
If x = 12 Then
strCol = "L"
Else
If x = 13 Then
strCol = "M"
Else
If x = 14 Then
strCol = "N"
Else
If x = 15 Then
strCol = "O" 'COLUMN
Else
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
rowStart = 1 'START SELECTION IN THIS ROW
rowOffset = 2 'SELECT EVERY x ROW
With ActiveSheet
Set rg = .UsedRange.Columns(1) 'determine last row
lastRow = rg.Cells(rg.Cells.Count).Row
Set rg = .Range(strCol & rowStart) 'set initial range
For i = rowStart + rowOffset To lastRow Step rowOffset 'loop
Set rg = Application.Union(rg, .Range(strCol & i))
Next
End With
If rg Is Nothing Then 'no cell
MsgBox "No cell"
Else
rg.Select
End If
With Selection.Interior
..ColorIndex = 35
..Pattern = xlSolid
End With
Next x
End Sub