My solution works Ian, I tested it :( :lol:

So you want each cell in the A column to be compared to the whole of column C?

Not sure there's a quick way to type that but that sounds to me like a hidden cell containing CONCAT(C2, C3, C4 .. C200) and compare against that.

quote:

---

Originally posted by Bart

The problem with the above is its only comparing A2 to C2, where it should be comparing A2 to C2:C200

---

Do you want one single cell to compare to the column?

Or the each cell in the column to compare the cell adjacent to it in the other column?

Doesn't need $ as I see it.

=IF(COUNTIF($C$2:$C$200,A2),"True","False")

Sorry Bartm copy the above, I've put A1 in the previous one

I see, stick this in D2 and drag it down (hide it if you don't want to see it):

=IF(COUNTIF($C$2:$C$200,A1),"True","False")

Then do your conditional formatting based on that?

edit - the $'s are important to fix the reference to C2:C200, otherwise then you drag it down it becomes C3:C201, C4:C202 etc.

[Edited on 23-01-2013 by pow]

any ideas?
Ive tried:
=EXACT(A2, C2:C210)

*edit*, just trying to get a true/false to begin with.
The problem with the above is its only comparing A2 to C2, where it should be comparing A2 to C2:C200


[Edited on 23-01-2013 by Bart]

Conditional formatting is the one here, it'll do it no problem

Condition formatting and there should be something in there that you can use as your match condition.

If not, find a suitable string function to do the match and use an IF() to give you a 1 or 0 or similar, and conditionally format on that.

Im after some help with a formula.

I have a column of information C2 - C200

Im entering information into columns A2-A200

If any of the text matches the text in C2-C200 I would like to fill the cell to the right with a red background (B2-B200).

Anyone know how this can be done?