I have an Excel spreasheet and in worksheet_1 I have a word in each cell from E2 to H51. On worksheet_2 I have a column of cells each with two words in them. I am trying to write a VBA macro that says; if a word from range E2:H26 and a word from range E27:H51 is in a cell in worsheet_2 then write a "No" in the next column in workshhet_2 but on the same row, else write a "Yes".

Many thanks for any help.

Stuart

Hi Stuart
why not use a worksheet formula. e.g. in B1 on sheet 2 enter
=IF(COUNTIF('sheet1'!$E$2:$H$51,"*" & A1 & "*"),"No","Yes")

--
Regards
Frank Kabel
Frankfurt, Germany


stuart wrote:
I have an Excel spreasheet and in worksheet_1 I have a word in each
cell from E2 to H51. On worksheet_2 I have a column of cells each
with two words in them. I am trying to write a VBA macro that says;
if a word from range E2:H26 and a word from range E27:H51 is in a
cell in worsheet_2 then write a "No" in the next column in
workshhet_2 but on the same row, else write a "Yes".

Many thanks for any help.

Stuart