Comparing two cells and if equal, compare two other different cells and highlight results if not equal

0
votes

I am trying to compare two cells on two different workbooks. If equal, the script should further compare two other different cells on those (same) workbooks and highlight the ones that aren't equal.

I've tried the following:

Sub Compare()
Dim mycell As Range
Dim shtSheet1 As Worksheet
Dim shtSheet2 As Worksheet
Set shtSheet1 = Workbooks("100Series").Worksheets("Report")
Set shtSheet2 = Workbooks("UserWorkbook").Worksheets("User")
For Each mycell In shtSheet2.UsedRange
  If Not mycell.Value = shtSheet1.Cells(mycell.Row, mycell.Column).Value Then
    mycell.Interior.Color = vbRed
   End If
Next
End Sub

However, this doesn't work. It throws the Subscript out of range error. I am not sure what is out of range. Each of the worksheets are 3500 rows long and 2 columns wide.

What's wrong with me current code?

2
vbaexcel
Do you want to compare Cell A1 of Worbook 1 with Cell A1 of Worbook 2 and so on (Cell B310 with Cell B310 of the other Workbook...)? - Benno Grimm
At what point does it throw the Subscript out of range error? If it is as it is doing the Set shtSheet1 = Workbooks("100Series").Worksheets("Report") command, it is probably because the workbook is called "100Series.xlsx" (or something similar). - YowE3K
When the error message appears, there should be a "Debug" button in the message box. If you click that button, the line causing the error should be highlighted in yellow - barrowc
Side advice, for performance i recommend pulling all values into a range variable, make a 3rd with result variable with "results" and compare that way. Going cell by cell is extremely slow compared to this method. - Holmes IV
1) edit workbook name with extention. 2) replace if not with if (remove not from the if ) and try - Karthick Gunasekaran

2 Answers

0
votes

Something like this?

Dim sht1 As Worksheet, sht2 As Worksheet
Dim y As Integer, x As Integer

Private Sub CompareStuff()

    Set sht1 = Workbooks("Wb1.xlsm").Worksheets("Sheet1")    'Make sure to pick the right name.
    Set sht2 = Workbooks("Wb2.xlsm").Worksheets("Sheet2")
    y = 1

    Do While y <= ActiveSheet.Columns("A").Cells.Find("*", _
                  SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
        For x = 1 To 2
            If sht1.Cells(y, x).Value <> sht2.Cells(y, x).Value Then
                sht1.Cells(y, x).Interior.ColorIndex = 3
                sht2.Cells(y, x).Interior.ColorIndex = 3
            End If
        Next x
        y = y + 1
    Loop

End Sub
0
votes

Thanks, well I came up with the following and it looks to be working fine. The thing is I want it to stop with the first two columns (in both the workbooks) and also ignore the spaces in the values. How do I go about doing that? 

Sub Compare()
    Dim shtSheet1 As Worksheet
    Dim shtSheet2 As Worksheet
    Set shtSheet1 = Workbooks("100Series").Worksheets("Report")
    Set shtSheet2 = Workbooks("UserWorkbook").Worksheets("User")
        For Each mycell In shtSheet1.UsedRange
          If Not mycell.Value = shtSheet2.Cells(mycell.Row, mycell.Column).Value Then
            mycell.Interior.Color = vbRed
           ElseIf mycell.Value = shtSheet2.Cells(mycell.Row, mycell.Column).Value Then
            If Not mycell.Offset(0, 1).Value = shtSheet2.Cells(mycell.Row, mycell.Column + 1).Value Then
                mycell.Interior.Color = vbRed
                End If
           End If
        Next
    End Sub