Highlight items that appear in seperate workbook

[Ur Face]

I am looking for a macro to work on the selected worksheet. Compare all cells that have data in them to a list in another work book. If any cells contain a match with the workbook i wanted to highlight them yellow.

Typical work sheet has Part number, Description, Quantity as the different columns. The Workbook master list (saved in local directory) has a list of part number and description. The list contains key items for review so they need to be highlighted.

In the end i am going to attach the macro to the ribbon so it will be easy to use regardless of the workbook i am in. Any help would be greatly appreciated and thank you in advance!

 

[vletm]

Ur Face
Hint:
Without samples from both Excel-files, there could be 'some' challenges to get exact working code.
You're asking like with two papers and ask same - compare & highlight - without seeing those papers.

 

[Ur Face]

Sorry i am not that advanced in VBA to see how this would have further challenges.

In my mind its as simple as
I have a workbook (key items workbook) that has two columns on sheet1
Column A
Partnumbers
001
002
Etc for hundreds

Column B
Description
Nut
Bolt
Screw
Etc for hundreds

I then get orders that come to me in the form of new workbooks that can have multiple sheets.
Each sheet will have a bunch of data.
Within this bunch of dadta will also be a description and partnumber column.

Column D
Description

Column G
partnumber

I wanted the macro to evaluate the order workbook and highlight any cells that have a matching description or part number with the key items workbook.

I know how modify macros and will be able to target the correct file location. I also know how to do other small modifications to get the macros to work. My problem so far has been finding the correct function to perform the duty from one workbook to another efficently and in a "smart manner". I mean smart manner as in being able to find the correct column to analyze on the order work boom incase description is on column C or is now column G in the next work book.

Im not able to share exact details and the documents due to company policy and i figured i will have to do moding to the code to wnsure proper working outcome.

 

[vletm]

Ur Face
I asked Excel-files - none code.
If Your company policy prevents to send sample Excel-files, then even those samples have to be more than top secret.
For me, You're offering to use to white papers which has something somewhere.
In that kind of case, the most of possible code would only searching something from somewhere.
Above means, this won't be for me.

 

[Ur Face]

Thats fine.
In my opinion you are greatly overthinking the problem and objective. This happens frequently with a greater knowledge base of the subject. In turn i could be greatly underestimating the difficulty of the problem because of my lack of knowledge in the subject.

Me not knowing the correct naming of the functions and process is preventing me from being able to search the proper method of writing the code out.

If we simplify it as much as possible:
Code that will search cells in the current workbook and compare them to a seperate workbook. Any matching items will be highlighted in the current workbook.

Looking up these phrases and trying a couple of the results out so far have been like hitting brick walls so far. I modify what i find in a way that i think will work but it does not. My big challenge seems to be the transition of crossing workbooks to get the "comparison" to work. If possible i would like to avoid making the system copy the columns from the key items work book to the order work book, then perform a comparison and then delete the columns. So far that is the only method i have had work but that has crashed a few times... not sure why.

 

[vletm]

Ur Face
Show me Your challenge with below files without adding anything there.
As in Your challenge, those files could be anywhere.

 

[Ur Face]

Empty 1 being the order file that will have a random name of the file dependent on what the order is.
Empty 2 being the "key items" file that will be saved in a known location.

Empty 2 would not typically be open.

Empty 1 (and many other workbooks) will be open but be the only selected workbook before i press the ribbon key thats linked to the macro for the analysis.

Expected outcome would be that the screw and plank and partnumbers matching will be highlighted on the empty 1 workbook in yellow.