Compare Two Sheets Generates Runtime Error 13

[Guest]

For several weeks the macro below worked fine:
Sub Compare2Shts()
For Each Cell In Worksheets("Primary").UsedRange
If Cell.Value <> Worksheets("Secondary").Range(Cell.Address) Then
Cell.Interior.ColorIndex = 3
End If
Next

For Each Cell In Worksheets("Secondary").UsedRange
If Cell.Value <> Worksheets("Primary").Range(Cell.Address) Then
Cell.Interior.ColorIndex = 3
End If
Next
End Sub



It would compare values in two sheets, and highlight any differences in red.
Today, all of a sudden, it just started erroring out at this line:
If Cell.Value <> Worksheets("Secondary").Range(Cell.Address) Then

I get a message saying “Run-time error 13â€. This is quite bizarre because,
as far as I can tell, nothing changed in the data set. Does anyone have any
suggestions as to what may cause this?


Kind Regards,
Ryan---

 

[Jim Cone]

Ryan,
You possibly have an error value in one of the cells being compared.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"ryguy7272"
wrote in message
For several weeks the macro below worked fine:
Sub Compare2Shts()
For Each Cell In Worksheets("Primary").UsedRange
If Cell.Value <> Worksheets("Secondary").Range(Cell.Address) Then
Cell.Interior.ColorIndex = 3
End If
Next

For Each Cell In Worksheets("Secondary").UsedRange
If Cell.Value <> Worksheets("Primary").Range(Cell.Address) Then
Cell.Interior.ColorIndex = 3
End If
Next
End Sub

It would compare values in two sheets, and highlight any differences in red.
Today, all of a sudden, it just started erroring out at this line:
If Cell.Value <> Worksheets("Secondary").Range(Cell.Address) Then
I get a message saying “Run-time error 13â€. This is quite bizarre because,
as far as I can tell, nothing changed in the data set. Does anyone have any
suggestions as to what may cause this?

Kind Regards,
Ryan---

 

[Guest]

Something in one of the far right-hand columns caused it to fail. Whatever
it is, it is very weird! I just changed the compare range; now the macros is
like this:
Sub Compare2Shts()
For Each Cell In Worksheets("Primary").Range("A1000:Z2500")
If Cell.Value <> Worksheets("Secondary").Range(Cell.Address) Then
Cell.Interior.ColorIndex = 3
End If
Next
etc...

I have no idea what it was...just one of those formatting-things I guess.
Maybe error value accidentally got into the UsedRange or something...who
knows. I learned a long time ago, just because you can't see some kind of
data (i.e. a space) doesn't mean that some kind of data is not there.

Thanks for the look Jim!!
Ryan---