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
[VBA]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[/VBA]
Reply With Quote
