conditional formatting based on a list

[jongooligan]

Got data validation in an input sheet that is based on a list. Turned off the error alert because the users need to sometimes enter values that are not on the list. We need to capture these values and maybe add them to the list if they occur frequently enough.
Is there a way of conditionally formatting these cells based on whether they appear in the list. Must be a non coding solution as this sheet will not be supported when I clear off (which will be soon).

 

[gnrboyd]

The HTML maker doesn't show color of the fonts for conditional formatting but here is an example of conditional formatting showing any name in the input box that isn't in the name box in red. (In my sheet, Frank and Johnathon are both shown in red font to identify them as not in the list.)

Here is the conditional formatting in cell C2:
Formula is COUNTIF($A$2:$A$6,C2)=0 - Then select font formatting of your choice. If the name entered in the input cell is in the table, the count will return 1 or more. If the name entered is not in the table, it will return 0 and show as a different font. (....with what you set up in the conditional formatting settings.)

You could add another column with this type of formula in it and then use filters to view all of the names with a count of 0 and add them that way as well. (I would prefer this method personally but your post asked about conditional formatting.) I added the extra column to my example. (column with green header)

Book1
	A	B	C	D
1	NamesList		InputNames	Count
2	Bob		Judy	1
3	Judy		Frank	0
4	Sam		Sam	1
5	Ted		Johnathon	0
6	Kathy		Judy	1
Sheet1