Quote Originally Posted by goonie
thanks a lot for the thoughts so far, definately given me something to think about!

it was suggested to me by another source that I can use conditional formatting with the COUNTIF() command. I've had a bit of success with this but haven't fully got it working yet.

Starguy, i have attached an example of the kind of sheet i will be working with (this is purely made names and data, so i'm not breaching any confidences!)

You should see two lists which i'm trying to compare. Ideally it will highlight any entries that are in the first list but not the second (in green say), any that are in the second but not the first (in orange say) and finally any that appear on both lists (lets go for red!)

I'm pretty close with the conditional formatting and countif(), but if you can help me cross the finishing line it will be much appreciated!

thanks!
please put here formula that you use.