colour cell if data does not match a list

**tuizner** · 03-10-2010, 11:59 PM

Hi

This seems simple but I cannot get it to work (or maybe it's just late)

I have a cell where data must be input. The data of that cell must match a list of 100 or so entries in another column. I would like the cell to show a message or color red if the data does not match that data set.

data set is in form
101-0012-NVY-10
101-0013-GOA-12
etc

Any ideas?

Thanks
Mat

**Paul** · 03-11-2010, 12:06 AM

Hi Mat, welcome to the forum - or at least your first post since you joined.

You can use conditional formatting for this. For example if you want to format A1 based on whether or not it matches any value in B1:B100, open the Conditional Format window and create a new rule using this formula:

=ISNUMBER(MATCH(A1,B1:B100,0))

Click the Format button and choose a cell Fill/Pattern color (e.g. Red).

Hope that helps!

**tuizner** · 03-11-2010, 06:37 AM

"=ISNUMBER(MATCH(A1,B1:B100,0))"

Thanks Paul. I can see how that works for a cell but I am looking at a range and simply changing A1 to A1:A99 in the above does not work.

In column A below I have input data. In column B I have set data. I need to highlight cells in column A when they do NOT match any of the data in column B. So A1 is OK as it matches B4 but the rest (A2:A8) should be highlighted.

A ---------------------------------- B

101-0002-AQU-12-----------101-0001-VAN-14
sdfsdf----------------------------101-0001-VAN-16
sfddsf----------------------------101-0002-AQU-10
sdfs-------------------------------101-0002-AQU-12
jhj
hj
hj
hjhjhj

Thanks again.
Mat

**martindwilson** · 03-11-2010, 06:45 AM

cf a1 as
=isna(match(a1,$b$1:$b$100,0)) or =COUNTIF($B$1:$B$100,A1)=0
then copy the format over the rest of cells in colA ,it will auto adjust to a2 a3 and so on

**tuizner** · 03-11-2010, 07:02 AM

Done it with the following:

selecting column A and conditional formatting, create 2 formats, one covering text and the other covering numbers to make sure any error in cell A is picked up:

=AND(ISTEXT(A1),A1<>$B$1:$B$999)
=AND(ISNUMBER(A1),A1<>$B$1:$B$999)

Thanks for the input Paul.
Mat

**tuizner** · 03-11-2010, 07:15 AM

Thanks Martin

Both of these work also - and are much more pretty than mine. Although I need to add a Zero to the data set to stop my input data column coming up highlighted this is easily achieved.

Thanks for the assistance