c.Resize
 

This part of my macro runs perfectly:

For Each c In Range("D6:D61")
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("D6:D61")
If c.Resize(, 15).Value = "" Then
c.Offset(, -1).Interior.ColorIndex = 6

But no luck.

Thanks

CR

c.Resize
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"CR" wrote in message
m...
This part of my macro runs perfectly:

For Each c In Range("D6:D61")
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("D6:D61")
If c.Resize(, 15).Value = "" Then
c.Offset(, -1).Interior.ColorIndex = 6

But no luck.

Thanks

CR

c.Resize
 

Hi

Try this:

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

Regards,
Per

"CR" skrev i meddelelsen
m...
This part of my macro runs perfectly:

For Each c In Range("D6:D61")
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("D6:D61")
If c.Resize(, 15).Value = "" Then
c.Offset(, -1).Interior.ColorIndex = 6

But no luck.

Thanks

CR

c.Resize
 

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("D6:D61")
If WorksheetFunction.CountA(c.Resize(, 15)) = 0 Then
c.Offset(, -1).Interior.ColorIndex = 6
End If
Next

Mike

"CR" wrote:

This part of my macro runs perfectly:

For Each c In Range("D6:D61")
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("D6:D61")
If c.Resize(, 15).Value = "" Then
c.Offset(, -1).Interior.ColorIndex = 6

But no luck.

Thanks

CR

c.Resize
 

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.



"Mike H" wrote in message
...
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("D6:D61")
If WorksheetFunction.CountA(c.Resize(, 15)) = 0 Then
c.Offset(, -1).Interior.ColorIndex = 6
End If
Next

Mike

"CR" wrote:

This part of my macro runs perfectly:

For Each c In Range("D6:D61")
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("D6:D61")
If c.Resize(, 15).Value = "" Then
c.Offset(, -1).Interior.ColorIndex = 6

But no luck.

Thanks

CR