Comparing Two Columns in Excel Row By Row?

Question

have currently browsed the forums and have came up with a code to compare two columns from two separate excel books and then highlight anything matching with the CompareRange. Here is a few more details about the problem:

I have two excel sheets. And data like this in each sheet:

(First Sheet)                             (Second Sheet)

•A         B                             N                O
•7        .7                             3               .56
•6        .6                             8               .45
•5        .5                             9               .55
•4        .4                            11               .2
•3        .3                             8               .22
•2        .2                             9               .55
•1        .1                             8               .54

As you can see, given this example nothing should be highlighted once the macro is run since nothing from Column A or B from the first sheet matches directly with Column N & O from the second sheet. The problem is that with the macro (module) I have come up with will highlight "3" from Column A and ".2" from Column B, just because they appear in Column N & Column O respectivally.

What I want: I only want a number to be highlighted if both the numbers "7" & ".7" are matched in the same row of Column N & Column O on the other spreadsheet. I have no idea how to do this. To be a little more precise, I'll give an example. Say I edited the data to be like this.

(First Sheet)                             (Second Sheet)
 •A        B                             N               O
•7        .7                             3               .56
•8        .45                           8               .45
•5        .5                             9               .55
•11        .4                            11               .2
•3        .3                             8               .22
•2        .2                             9               .55
•1        .1                             8               .54

With this data, I would want the second row of A & B ("8" & ".45") highlighted, while my error "3" of Column A and ".2" of Column B is not highlighted. Also, I would like it if row 4 of Column A & B ("11" & ".4") is not highlighted at all either, just because in O it is .2 and in B it would be .4 even though the 11's match.

Please advise. Thanks in advance.

Attached is the macro/module I have entered in which is working kind of correctly but producing the mistake.

And also, (kind of a lesser problem), both the files with data will have the same header, example would be if Column A & Column N both had "Dogs" as it's title in Row 1 and Column B & O both had "Cats" as it's title in Row 1. Is there anyway the macro can be adjusted so it compares those two columns between the two workbooks without me even having to select or assigning a range? Thank you so much.

Sub Find_Matches()
Dim Column1 As Range
Dim Column2 As Range
Set Column1 = Application.InputBox("Select First Column to Compare", Type:=8)
If Column1.Columns.Count > 1 Then
    Do Until Column1.Columns.Count = 1
        MsgBox "You can only select 1 column"
        Set Column1 = Application.InputBox("Select First Column to Compare", Type:=8)
    Loop
End If
    Set Column2 = Application.InputBox("Select Second Column to Compare", Type:=8)
If Column2.Columns.Count > 1 Then
  Do Until Column2.Columns.Count = 1
    MsgBox "You can only select 1 column"
    Set Column2 = Application.InputBox("Select Second Column to Compare", Type:=8)
  Loop
End If
  If Column2.Rows.Count <> Column1.Rows.Count Then
Do Until Column2.Rows.Count = Column1.Rows.Count
  MsgBox "The second column must be the same size as the first"
  Set Column2 = Application.InputBox("Select Second Column to Compare", Type:=8)
Loop


End If
  If Column1.Rows.Count = 65536 Then
    Set Column1 = Range(Column1.Cells(1), Column1.Cells(ActiveSheet.UsedRange.Rows.Count))
    Set Column2 = Range(Column2.Cells(1), Column2.Cells(ActiveSheet.UsedRange.Rows.Count))
  End If
    Dim CompareRange As Variant, x As Variant, y As Variant
    ' Set CompareRange equal to the range to which you will
    ' compare the selection.
    Set CompareRange = Workbooks("Book4").Worksheets("Sheet1").Range("N2:N7")
    Set CompareRange1 = Workbooks("Book4").Worksheets("Sheet1").Range("O2:O7")
    ' NOTE: If the compare range is located on another workbook
    ' or worksheet, use the following syntax.
    ' Set CompareRange = Workbooks("Book2"). _
    '   Worksheets("Sheet2").Range("C1:C5")
    '
    ' Loop through each cell in the selection and compare it to
    ' each cell in CompareRange.
    For Each x In Column1
        For Each y In CompareRange
            If x = y Then
            x.Interior.Color = vbYellow
            End If


        'x.Offset(0, 5) = x
    Next y
Next x
For Each x In Column2
    For Each y In CompareRange1
        If x = y Then
        x.Interior.Color = vbYellow
        End If
        'x.Offset(0, 5) = x
    Next y
Next x

End Sub

Answer 1

Replace both of your loops with one that compares both pairs of cells at the same time:

For i = 1 To Column1.Rows.Count
    For j = 1 To compareRange.Rows.Count
        If Column1.Cells(i, 1) = compareRange.Cells(j, 1) Then
            If Column2.Cells(i, 1) = compareRange1.Cells(j, 1) Then
                Column1.Cells(i, 1).Interior.Color = vbYellow
                Column2.Cells(i, 1).Interior.Color = vbYellow
            End If
        End If
    Next j
Next i