Worksheet_Change Error - "Unable to get the Interior Property of the Range class"

[marimar02]

Hello,

I have a very automated spreadsheet that allows me to copy/paste data from a worksheet to other worksheets within the same book. Recently I added below event to automatically hightlight cells based on the criteria within the code. My trouble is that I get the "Run-time error '1004': Unable to get the Interior property of the Range class" whenever I have this code in and the destination cell is empty. If the destination cell is full, the error doesn't pop up.

Problem code:

Range(Cells(r.Row, 1), Cells(r.Row, 5)).Interior.ColorIndex = xlColorIndexNone

When I take above code out, I'm able to copy/paste without problem. I need the code to be able to reset the color whenever I take the criteria out.

Here is the full code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Dim i As Range
    For Each r In Range("C10:C109")
      
        If IsDate(Right(r, 8)) Then
            
            Range(Cells(r.Row, 1), Cells(r.Row, 5)).Interior.ColorIndex = 44
            
        Else
    
            Range(Cells(r.Row, 1), Cells(r.Row, 5)).Interior.ColorIndex = xlColorIndexNone
        
        End If
    
    Next r
    For Each i In Range("C10:C109")
        If Right(i, 4) = "Zero" Then
            Range(Cells(i.Row, 1), Cells(i.Row, 5)).Interior.ColorIndex = 6
            
        End If
    
    Next i
End Sub

Thank you

 

[wigi]

Why do color 100 cells at every change of a cell in the said worksheet?

Wouldn't it make more sense to use Target ? (i.e. the cell(s) that have changed)

 

[marimar02]

it's because I type the date or word "zero" manually to account names affected.

 

[wigi]

That's not really an answer to my question.

Why do you colour the cells A10:E109 if you only change 1 or a few cells (the variable Target)?

 

[Jonmo1]

I think what wigi is saying is that it is very inefficient to recolor every cell in C10:C109, when only 1 or a few have actaully changed.
You can restrict the code to only color the cells that actually changed.

Also, your code runs every time ANYTHING on the sheet changes, not only the cells in C10:C109.
And one more, you don't need to loop through the range twice, you can do both tests (date or zero) within the same loop.

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, MyRange As Range

Set MyRange = Intersect(Target, Range("C10:C109"))
If Not MyRange Is Nothing Then
    For Each r In MyRange
        If IsDate(Right(r, 8)) Then
            Range(Cells(r.Row, 1), Cells(r.Row, 5)).Interior.ColorIndex = 44
        ElseIf Right(r, 4) = "Zero" Then
            Range(Cells(r.Row, 1), Cells(r.Row, 5)).Interior.ColorIndex = 6
        Else
            Range(Cells(r.Row, 1), Cells(r.Row, 5)).Interior.ColorIndex = xlNone
        End If
    Next r
End If
End Sub

Now, this will ONLY run if a cell in C10:C109 changes, nothing else.
AND, it will only color the cells that were actually changed, instead of recoloring a cell that didn't change.


Hope that helps.

 

[marimar02]

Date	Account #	Account Name	Settlement Type	Amount
06.06.12	654321	Test Account	Check	$ 50.00
06.06.12	654321	Test Account - 06.05.12	Check	$ 100.00
06.06.12	654321	Test Account	Check	$ 75.00
06.06.12	654321	Test Account	Check	$ 100.00
06.06.12	654321	Test Account - 06.05.12	Check	$ 75.00

Please see below. I hope it makes sense. "Date", "Account #", etc. represent columns "A", "B", etc. and start with row 10. Whenever I type the date or word "zero" behind the account name, all 5 cells in that row only should change color. It doesn't change color for all cells A10:E109 but rather row by row based on the criteria in that row's "C" cell.

Thanks for your help...<tbody>
</tbody>

 

[marimar02]

hmmm. My text disappeared from above example but I see the inefficiency of the code now. I will try the code provided by jonmo1 in a minute.

 

[marimar02]

This worked perfectly. Thank you.

 

[marimar02]

Thank you wigi. Looking at jonmo1's code, i see what you were telling me. This fixed the issue and now I can also use "undo" for the rest of the sheet.

Mario

 

[wigi]

Glad it's solved now