Highlight cell if it appears on another sheet?

Hi Guys, I hope you can help.

I have a list of numbers in one sheet which I would like to highlight if they appear in a range of cells on another sheet?

I've attached a sheet to explain what I'm trying to do.

Any help would be greatly appreciated.

Thanks, James

You can't use references to other sheets in Conditional Formatting criteria, but you could use this formula in F5, say:

=ISNUMBER(MATCH("*"&D5&"*",'Sheet 2'!$C$4:$C$6,0))

and copy this down. Then you could use the results of this column (either TRUE or FALSE) to trigger conditional formatting on column D.

Hope this helps.

Pete

P.S. Are you still using Excel 2003? If not, please update your profile.

It looks like the OP has initiated a new thread to continue discussion on this topic.

http://www.excelforum.com/excel-gene...her-sheet.html

Geoff, I think you linked back to this thread - here's the other one:

https://www.excelforum.com/excel-gen...her-sheet.html

Pete

@Pete - sorry for the error, you are of course right.