VBA - Find differences in two columns

[mprivitt]

Hey all,

I'm new to the forums here, but I'm trying hard to get a project done and can't quite figure out how to do this macro...

Problem:

I have two excel workbooks. I want to write a macro that, once clicked, asks me which workbooks I would like to compare. Then, the macro compares column A in workbook 1 with column A in workbook 2 and highlights the numbers in workbook 2 that ARE NOT in workbook 1. The numbers are in no particular order. I merely would like to highlight the numbers in workbook 2, column A that are not in workbook 2, column A.

Help?

 

[MikeDBMan]

This would do it:

Sub HighlightNotIN()
Dim X, Y As Long
Dim Ans As Variant
Dim DataArray(60000, 2) As Variant
Beep
Let Ans = MsgBox(Prompt:="Which book do you want to start in, A-Yes or B-No?", Title:="Your Title", Buttons:=vbCritical + vbYesNo)
If Ans = 6 Then 'they hit Yes
    Windows("FileA.xlsx").Activate
ElseIf Ans = 7 Then  'they hit no
    Windows("FileB.xlsx").Activate
End If
For X = 1 To 60000
    If Len(Cells(X, 1).Value) > 0 Then
        DataArray(X, 1) = Cells(X, 1).Value
    Else
        DataArray(X, 1) = Empty
    End If
Next

If Ans = 6 Then 'they hit Yes
    Windows("FileB.xlsx").Activate
ElseIf Ans = 7 Then  'they hit no
    Windows("FileA.xlsx").Activate
End If
For X = 1 To 60000
    If Len(Cells(X, 1).Value) > 0 Then
        found = 0
        For Y = 1 To 60000
            If Cells(X, 1).Value = DataArray(Y, 1) Then
                found = 1
                Exit For
            End If
        Next
        If found = 0 Then 'never found it!
            Cells(X, 1).Select
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 65535
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        End If
    End If
Next
                    
    
End Sub

 

[mprivitt]

Hey Mike,

Thank you so much for the help. How should I handle the "FileA.xls" bit of the code?

 

[mprivitt]

Having trouble executing this code. It works for a min and nothing happens :/

Thank you for your help, Mike

 

[MikeDBMan]

FileA, FileB would be where you would substitute in your two files. You said you had two files. I didn't know what their names were so I just used FileA and FileB. Regarding your comment below about it works for a min and nothing happens, my guess is that it finished. This would only take a short, short time to actually run. I'd suggest checking the results. It seemed to work just as you asked when I ran it.

 

[mprivitt]

Should I put the name of my files in each of the quotations? And should they both be open when I execute the macro?

 

[MikeDBMan]

I did not put anything in the code to test to see if they are open or not so yes you'd have to have them both open. And the names of the files (with extensions) would need to be in quotations just like I did.

 

[mprivitt]

Mike,

I seem to be having an issue with your code - each time the code executes, the "working" logo takes over for about 6 seconds, then stops. Nothing is changed. Nothing is highlighted.

Am I doing something incorrectly? I have

Sub HighlightNotIN()
Dim X, Y As Long
Dim Ans As Variant
Dim DataArray(60000, 2) As Variant
Beep
Let Ans = MsgBox(Prompt:="Which book do you want to start in, A-Yes or B-No?", Title:="Your Title", Buttons:=vbCritical + vbYesNo)
If Ans = 6 Then 'they hit Yes
Windows("IRAsheet.xls").Activate
ElseIf Ans = 7 Then 'they hit no
Windows("IRAsheet_2.xls").Activate
End If
For X = 1 To 60000
If Len(Cells(X, 1).Value) > 0 Then
DataArray(X, 1) = Cells(X, 1).Value
Else
DataArray(X, 1) = Empty
End If
Next
If Ans = 6 Then 'they hit Yes
Windows("IRAsheet.xls").Activate
ElseIf Ans = 7 Then 'they hit no
Windows("IRAsheet_2.xls").Activate
End If
For X = 1 To 60000
If Len(Cells(X, 1).Value) > 0 Then
found = 0
For Y = 1 To 60000
If Cells(X, 1).Value = DataArray(Y, 1) Then
found = 1
Exit For
End If
Next
If found = 0 Then 'never found it!
Cells(X, 1).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
End If
Next
End Sub

Both windows are open and the columns A in each spreadsheet have different entries here and there.

 

[mprivitt]

And when I said

I merely would like to highlight the numbers in workbook 2, column A that are not in workbook 2, column A.

, I meant I desire to highlight the numbers in workbook 2, column A that are not in Workbook ONE, column A.

 

[mprivitt]

Hey Mike,

I got the code to run - turns out that there is a section of the code accidentally copied twice. I deleted the first instance of it and it's starting to work...but we have another error.

THe code is highlighting ALL the cells in column A of the workbook in which it starts - not only the ones that differ from the other workbook.