Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Resize | Excel Programming | |||
I could NOT resize the axis title but excel allows me to resize gr | Charts and Charting in Excel | |||
RESIZE PROPERTY: is it possible to resize "up" and "to the left"? | Excel Programming | |||
I could NOT resize the axis title but excel allows me to resize gr | Charts and Charting in Excel | |||
Need some help with Resize. Thanks | Excel Programming |