ekw590
07-16-2010, 01:13 PM
I'm getting run-time error '1004':application-defined or object defined error for this line of code: 'If R.EntireRow.Cells(1, DataTestColumn).Value <> vbNullString Then' Full code below.
This FormatCellFill macro starts in cell A4 colorbanding every other 30 rows of groupings of data until it gets to a blank row then it restarts such that the last color banding will be less then 30. Any ideas as to what is wrong with the code?
Thanks Evan
Sub FormatCellFill()
Dim R As Range
Dim LastRow As Long
Dim ColorIndex As Long
Dim InFill As Boolean
Dim N As Long
Dim ColorWidthColumns As Long
Dim StartCell As String
Dim DataTestColumn As String
ColorIndex = 8 'cyan '<<<<<< Set color index
InFill = True '<<<<< True -> Color first group, False-> No color first group
ColorWidthColumns = 10 '<<<< how many columns wide to format
StartCell = "A4" '<<<< where to start the banding
DataTestRow = 4 '<<<< column number containing data to test
Set R = Range(StartCell)
With R.Worksheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
N = 0
Do Until R.Row > LastRow
N = N + 1
If R.EntireRow.Cells(1, DataTestColumn).Value <> vbNullString Then
If InFill = True Then
R.EntireRow.Cells(1, "A").Resize(, ColorWidthColumns).Interior.ColorIndex = ColorIndex
Else
R.EntireRow.Cells(1, "A").Resize(, ColorWidthColumns).Interior.ColorIndex = xlColorIndexNone
End If
Else
N = 0
InFill = True
End If
If N >= 30 Then
N = 0
InFill = Not InFill
End If
Set R = R(2, 1)
Loop
End Sub
This FormatCellFill macro starts in cell A4 colorbanding every other 30 rows of groupings of data until it gets to a blank row then it restarts such that the last color banding will be less then 30. Any ideas as to what is wrong with the code?
Thanks Evan
Sub FormatCellFill()
Dim R As Range
Dim LastRow As Long
Dim ColorIndex As Long
Dim InFill As Boolean
Dim N As Long
Dim ColorWidthColumns As Long
Dim StartCell As String
Dim DataTestColumn As String
ColorIndex = 8 'cyan '<<<<<< Set color index
InFill = True '<<<<< True -> Color first group, False-> No color first group
ColorWidthColumns = 10 '<<<< how many columns wide to format
StartCell = "A4" '<<<< where to start the banding
DataTestRow = 4 '<<<< column number containing data to test
Set R = Range(StartCell)
With R.Worksheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
N = 0
Do Until R.Row > LastRow
N = N + 1
If R.EntireRow.Cells(1, DataTestColumn).Value <> vbNullString Then
If InFill = True Then
R.EntireRow.Cells(1, "A").Resize(, ColorWidthColumns).Interior.ColorIndex = ColorIndex
Else
R.EntireRow.Cells(1, "A").Resize(, ColorWidthColumns).Interior.ColorIndex = xlColorIndexNone
End If
Else
N = 0
InFill = True
End If
If N >= 30 Then
N = 0
InFill = Not InFill
End If
Set R = R(2, 1)
Loop
End Sub