Find data on Archive list & highlight row

AussieJac

Board Regular
Joined
Jul 7, 2005
Messages
57
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.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

AussieJac

Board Regular
Joined
Jul 7, 2005
Messages
57
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...... :cry:

Code:
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]
 

Forum statistics

Threads
1,136,272
Messages
5,674,749
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top