Search and highlight cells

[ghard1]

Hi everyone,

I have 3 worksheets A, B and C. Worksheet A and B have numbers in three columns. In worksheet C, column D, I would like a formula or conditional formatting which will search worksheet A for the number in column D and if it finds the same number as the one in the cell, it highlights the cell Green. I would also like it to search worksheet B and if the number is in worksheet B, highlight the cell Red. In no cases will the number be in both worksheets (A and B). Does this make sense? Is it possible?

Thanks for any help you can shed on this.

 

[Andrew Fergus]

My mistake - I assumed the lookup range in sheets A and B was one column wide - but re-reading your post I see you didn't actually say that. Be aware that if your named range includes columns other than columns you want to search, and assuming you use the countif approach, then you run the risk of getting a false positive, so restrict the named range to the column(s) you actually want to search.

Cheers
Andrew