Macro to check duplicates from 2 different workbook for specific number

[IKHAN]

HAVE TO COMPARE COLUMN C AND COLUMN A ON WORK BOOK 1 AND WORKBOOK 2 RESPECTIVELY AND HIGHLIGHT CELL (GREEN) UNMMATCHED IN COLUMN A IN WORK BOOK 2


example :


work book 1 :


Column C has some characters and numbers ex :


1.APPLE 4543 (6257) TEST

2.ORANGE INC2324


WORK BOOK 2 :


COLUMN A


4543

2324

7876

7643


OUTPUT : BELOW 2 NUMBERS TO BE HIGHLIGHTED IN GREEN IN WORK BOOK 2,Since unmatched in column c workbook 1


7876

7643


Help is really appreciated!!!

 

[Luke M]

You'll need to change the definitions for the search range and number range, but this should work for you.

[pre]

Sub ColorList()
Dim SearchRange As Range
Dim NumberRange As Range
Dim SearchValue As String
Dim xCell As Variant

'Where is the search list?
Set SearchRange = Workbooks("Book1.xls").Worksheets("Sheet1").Range("A2:A100")
'Where is list of numbers?
Set NumberRange = Workbooks("Book2.xls").Worksheets("Sheet1").Range("A2:A10")

For Each c In NumberRange
SearchValue = c.Value

On Error Resume Next
Set xCell = SearchRange.Find(what:=SearchValue, matchbyte:=False)
On Error GoTo 0
If xCell Is Nothing Then
c.Interior.ColorIndex = 3
Else
c.Interior.ColorIndex = 0
End If
Next c
End Sub

[/pre]

 

[IKHAN]

Get error msg "variable not defined for c"


Have option explicit defined for another macro

 

[Luke M]

In the definitions at the beginning of macro, add a line for

[pre]

Dim c as Range

[/pre]

 

[IKHAN]

Not sure if iam doing it right , Have both files book1.xls and book2.xls are on in my local folder.Cross checked the sheet name and workbook name..


Get error msg - Subscript out of range and points to


Set SearchRange = Workbooks("Book1.xls").Worksheets("Sheet1").Range("A2:A100")

 

[SirJB7]

Hi, IKHAN!

If you've yet crosschecked file and workbook names, did the same for worksheet names?

If so, sure you've got both workbooks opened in same Excel instance where macro is running?

Regards!