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

marimar02

Board Regular
Joined
May 21, 2010
Messages
107
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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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
Joined
May 21, 2010
Messages
107
it's because I type the date or word "zero" manually to account names affected.
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

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
Joined
May 21, 2010
Messages
107
DateAccount #Account NameSettlement TypeAmount
06.06.12654321Test AccountCheck $ 50.00
06.06.12654321Test Account - 06.05.12Check $ 100.00
06.06.12654321Test AccountCheck $ 75.00
06.06.12654321Test AccountCheck $ 100.00
06.06.12654321Test Account - 06.05.12Check $ 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

Board Regular
Joined
May 21, 2010
Messages
107

ADVERTISEMENT

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
Joined
May 21, 2010
Messages
107
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

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Glad it's solved now :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,867
Messages
5,598,542
Members
414,245
Latest member
allyciv

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top