Building a macro to look into another datasheet

I was asked to post on this forum as I was using a wrong one the first time. I need help in building the following macro.
----------------------------------------

I have two excel files:

products.xls
keywords.xls

products.xls:
column C: Is the list of colors for each product
column D: I want put the narrowed names resulting from the match when I run the macro.
keywords.xls
column A: Is a list of color names
column B: Is the narrowed name I assigned for each color name on column A.

Example: I assigned “red” as the narrowed name for the color names Light Red, Dark Red and Fire Red.
I want put in column D (file products.xls) Red if in the column C (file products.xls) exist Light Red, Dark Red or Fire Red. I don’t want repeat the word “red” in Column D, even if there are more than one match per row.

Actual match: if we take row 4, column C (file products.xls), the list is:
Black, Dark Green, Navy Blue, NG Black, NG Dk Green, Dark Blue, Light Blue, Royal Blue.

I want, in the D (file products.xls):

Black, Green, Blue

Can anyone help me on this please. Thank you in advance.

maybe udf

Please Login or Register  to view this content.

Hello nilem,

Your code works exactly as I needed.

I just have a question; it is possible to have the keywords in a separate file instead of in another sheet of same document?

The reason is that I have about 40 excel files to run this update every week and going one by one with the keywords update is time consuming. I though in a separate file so the keyword list should be edited only one time.

Thank you. I really appreciate it.