c.Resize

[CR]

This part of my macro runs perfectly:

For Each c In Range("D661")
If c.Resize(, 15).Interior.ColorIndex = xlNone Then
c.Offset(, -1).Interior.ColorIndex = 6

It runs though the rows, checks for xlNone and changes the color of the
offset cell.

I would also like it to check the rows and if all cells in a row are blank,
change the color of the offset cell.

I have tried several variations such as:

For Each c In Range("D661")
If c.Resize(, 15).Value = "" Then
c.Offset(, -1).Interior.ColorIndex = 6

But no luck.

Thanks

CR

 

[Don Guillett]

Sub colorifblankxx()
For Each c In Range("D28")
If Application.CountA(c.Resize(, 15)) < 1 Then
c.Offset(, -1).Interior.ColorIndex = 6
End If
Next c
End Sub

 

[Per Jessen]

Hi

Try this:

For Each c In Range("D661")
If WorksheetFunction.CountBlank(c.Resize(, 15)) = 15 Then
c.Offset(, -1).Interior.ColorIndex = 6
End If
Next

Regards,
Per

 

[Mike H]

Hi

This will clas with the first check because your color filling the same
range but here's the corected code for the second part

For Each c In Range("D661")
If WorksheetFunction.CountA(c.Resize(, 15)) = 0 Then
c.Offset(, -1).Interior.ColorIndex = 6
End If
Next

Mike

 

[CR]

It's the same range but filling on a different criteria. And I am going to
change the index to 4.

Thank you all for the help. It now works.