Find data on Archive list & highlight row

[AussieJac]

I have two separate workbooks. I want to run a macro to identify specific records (based on contract number in Column C) listed in Workbook One that are also listed in Workbook Two (contract number in Column B). I then want to change the colour of that line of data in Workbook Two.

Workbook One contains a list of data from A3:K30 which changes each week.
Workbook Two contains 19,551 records (basically an archive list)

Workbook Two sheet names:
Totals
List A
List B
List C
List D
Archive
(I only want to check for a match in sheets List A,B,C,D)


I currently use an index/match formula but because the data can be on sheets A,B,C,or D etc I have to change the formula constantly.

 

[AussieJac]

Example of code tried

I have tried to come up with a macro based on some other postings I've read but no joy so far.......

Example below......

Dim currentfile As String
Dim WS As Worksheet
Dim R As Range


    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Workbooks.Open Filename:= _
        "G:DOD.xls"
    MsgBox ("Opening DataBase")
    
    Windows("Matching Data.xls").Activate
    Sheets("Matching Data").Select
    [L3].Select
       For Each WS In Sheets(Array("List A", "List B", "List C", "List D", "List E", "List F"))
            Set R = WS.Range("b2:b3000").Find(ActiveCell.Offset(0, -9))

            If Not R Is Nothing Then
                ActiveCell.Offset(0, 2).Value = "Found"
        Exit For
            End If
        Next

End Sub[quote][/quote]