I 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