Nov 15 2021 08:30 AM
Hello,
I am trying to use conditional formatting on a column that looks like
Dog
Dog
Doog
Cat
Kat
Elephant
Elephant
Elefant
In this case I would like "Doog", "Kat", and "Elefant" to be highlighted, aka anything that is a typo or does not fit my list of proper or standardized names.
I created a second sheet with the names that are standard, and anything that is NOT included on this list I would like highlighted in red. Is there a simple way to do this? The list has 187 unique names.
Nov 15 2021 08:42 AM
SolutionLet'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.
Nov 15 2021 08:58 AM
Nov 16 2021 11:15 AM
Nov 16 2021 11:34 AM
Nov 15 2021 08:42 AM
SolutionLet'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.