Conditional Formatting to deal with case sensitivity

[glennpc]

Hui helped me the other day with a formula to use to conditionally format cells in one range that didn't have a match to a set of cell values in another column. It worked great. But now I'm applying it to a state column, and it doesn't quite get it all.

I have a column, column B, where I have all my state abbreviations, AL through WY. In another column I have what the users have entered:

Alabama
AL
NY
North Carolina
nj
Nj
VA
va

Hui's formula flags Alabama and North Carolina correctly. It does not flag nj, Nj, or va. Those need to be flagged because we only want the users to enter one of the allowable 2-character codes that are upper case. So lower case or mixed case entries need to be flagged. The COUNTIF seems to ignore case-- it considers NJ = Nj = nj so the ones that have lower case don't get flagged.

Do I need to run a second conditional formatting rule AFTER the one Hui gave me or is there one rule that would catch all of the items that don't exactly match AL, AK, AR, etc. ?

 

[Chihiro]

Try something like
=ISERROR(MATCH(1,--EXACT($A$1:$A$6,E1),0))

See attached taking your original sheet as example. (Took out "People" from A, and shows People in red font since case does not match with people).

 

[glennpc]

This worked great! Thanks so much, you saved me a ton of researching to figure this thing out.