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

marimar02

Board Regular
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:

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

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

wigi

Well-known Member
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)

Last edited:

marimar02

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

wigi

Well-known Member
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

MrExcel MVP

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

Code:
``````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.

Last edited:

marimar02

Board Regular
 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.

</tbody>

marimar02

Board Regular

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

Board Regular
This worked perfectly. Thank you.

marimar02

Board Regular
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

Replies
2
Views
59
Replies
1
Views
60
Replies
8
Views
127
Replies
7
Views
67
Replies
4
Views
149

1,127,199
Messages
5,623,321
Members
415,966
Latest member
ctorohuamanchumo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back