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:
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:
Thank you
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:
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:
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