Comparing Problems

[Jim Berglund]

Private Sub Compare()
Dim x, y As Integer
Dim r, s As Variant
I'm trying to compare the contents of two worksheets, highlighting the
diffferences.
I thought it would be simple, and created the following - which doesn't
work.

Application.ScreenUpdating = False

For y = 1 To 25
For x = 1 To 100
Worksheets("D390").Select
r = Cells(x, y).Text
Worksheets("D390New").Select
s = Cells(x, y).Text
If s <> r Then
Cells(x, y).Select
With Selection.Interior
.ColorIndex = 6
End With
End If
Next
Next

End Sub
Could someone please explain what I've misssed or done wrong? Thanks
Jim Berglund

 

[Jim Cone]

The following untested revision to your sub should be closer to what you want.
The key is to qualify each range call out with the appropriate worksheet.
Also, a row index should be Long not an Integer.
And, you must specify each data type for a variable or you get a Variant.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)
(take a look at the XL Companion add-in)


Private Sub CompareR1()
'not tested
Dim x As Long, y As Long
Dim r As String, s As String
Dim wsOld As Worksheet
Dim wsNew As Worksheet

Application.ScreenUpdating = False
Set wsOld = Worksheets("D390")
Set wsNew = Worksheets("D390New")

For y = 1 To 25
For x = 1 To 100
r = wsOld.Cells(x, y).Text
s = wsNew.Cells(x, y).Text
If s <> r Then
wsOld.Cells(x, y).Interior.ColorIndex = 6
End If
Next
Next
Set wsOld = Nothing
Set wsNew = Nothing
Application.ScreenUpdating = True
End Sub
'----------


"Jim Berglund"
<[email protected]>
wrote in message
Private Sub Compare()
Dim x, y As Integer
Dim r, s As Variant
I'm trying to compare the contents of two worksheets, highlighting the
diffferences.
I thought it would be simple, and created the following - which doesn't
work.

Application.ScreenUpdating = False

For y = 1 To 25
For x = 1 To 100
Worksheets("D390").Select
r = Cells(x, y).Text
Worksheets("D390New").Select
s = Cells(x, y).Text
If s <> r Then
Cells(x, y).Select
With Selection.Interior
.ColorIndex = 6
End With
End If
Next
Next
End Sub
Could someone please explain what I've misssed or done wrong? Thanks
Jim Berglund

 

[dan dungan]

Where did you put the code?
Which version of excel are you using?