Compare two Workbooks to update a price List

[jwo1214]

I have two workbooks, both contain a price list of parts. One is a master list that I will call workbook "A" which contains every single part we sell. The other is a smaller list that I will call workbook "B" which contains frequently bought parts. What I want to do is compare column B in workbook "B" with column A in workbook "A" and update the price in workbook "A" with the price in workbook "B" and highlight the entire row where the price was updated. The price column is in D for both workbooks.

Can anyone help me with this?

Thanks!

 

[jwo1214]

bump

 

[Peter_SSs]

You didn't tell us the name of the relevant sheet in each workbook so I have assumed that in each case it is 'Sheet1'. Change code if that is not the case.

I have also assumed ...

- That both workbooks are open when the code is run.

- That both workbooks are in Excel 97 - 2003 format (.xls) not Excel 2007 format (.xlsx or .xlsm)

See how this goes and post back if you need modifications that you can't figure out yourself.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> UpdatePrices()<br>    <SPAN style="color:#00007F">Dim</SPAN> wsA <SPAN style="color:#00007F">As</SPAN> Worksheet, wsB <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range, FoundPart <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wsA = Workbooks("A.xls").Sheets("Sheet1")<br>    <SPAN style="color:#00007F">Set</SPAN> wsB = Workbooks("B.xls").Sheets("Sheet1")<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> wsB.Range("B2", wsB.Range("B" & wsB.Rows.Count).End(xlUp))<br>        <SPAN style="color:#00007F">Set</SPAN> FoundPart = wsA.Columns("A").Find(What:=c.Value, After:=wsA.Range("A1"), _<br>                        LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _<br>                        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)<br>        <SPAN style="color:#00007F">If</SPAN> FoundPart <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            MsgBox "Part " & c.Value & " not found in main list"<br>        <SPAN style="color:#00007F">Else</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> FoundPart<br>                .Offset(, 3).Value = c.Offset(, 2).Value<br>                .Resize(, .Parent.UsedRange.Columns.Count).Interior.ColorIndex = 37<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    <SPAN style="color:#00007F">Set</SPAN> wsA = Nothing: <SPAN style="color:#00007F">Set</SPAN> wsB = Nothing<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>