PDA

View Full Version : Color Banding Rows



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

Bob Phillips
07-16-2010, 02:27 PM
Maybe try



If R.EntireRow.Cells(1, DataTestColumn).Value <> "" Then

mikerickson
07-16-2010, 05:00 PM
TestDataColumn = ""

Cells(1, "") is an error