Forum Discussion
Conditional Formatting "not containing" for multiple terms
Let's say the list to be checked is in D2:D500 on Sheet 1, and the list of standard names is in A2:A188 on Sheet 2.
Select D2:D500 on Sheet 1. D2 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=ISERROR(MATCH(D2,'Sheet 2'!$A$2:$A$188,0))
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK twice.
Let's say the list to be checked is in D2:D500 on Sheet 1, and the list of standard names is in A2:A188 on Sheet 2.
Select D2:D500 on Sheet 1. D2 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=ISERROR(MATCH(D2,'Sheet 2'!$A$2:$A$188,0))
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK twice.
- Is there a way to specify that it is an error if it doesn't match the other column exactly? I noticed when the cells I would like highlighted for errors contains part of the reference column they do not get highlighted.
For example, if "Dog" is on my list of words that are ok, but I type "Dogs" and it is not recognized as an error, but I would like it to be considered an error.
Thank you! - Thank you!
