Conditional format a cell if value is found in list on another sheet

Good day, Gurus.

Not sure if this can be done. Column T in my workbook requires an entry, on any number of rows, for the country of origin. The entry in that column must exactly match the abbreviation for that country, which I have stored in a list on a separate hidden worksheet.

Do I use conditional formatting or data validation to color this cell red if a user enters a value not found in my list, and can you please offer an example of how to best accomplish it?

Thanks in advance for any help you can offer.

hi there. i suggest you use Data Validation. it will stop them immediately rather than to highlight & they have to try and error. i'll share both methods anyway:
1. say abbreviation list is in Sheet2!A2:A4. go to Sheet2 & select A2:A4.

2. you should see a name box below the ribbon & on the left of the formula bar. it shows the cell reference. click on that & type a name. i'll type Abbrev. Press ENTER

3. (i) for data validation, go to Sheet1 & select whichever rows you need them to fill (say T2:T10). go to Data -> Data Validation -> Allow: List -> Source:
=Abbrev
(ii) for Conditional Formatting, go to Sheet1 & select from U2:U10 (just so i can show you both methods). go to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
=AND(COUNTIF(Abbrev,U2)=0,U2<>"")

4. for (ii), format to the colour you desire & click ok

Hutch.xlsx

Assuming that
-- You have list of abbreviations on Sheet2!A2:A10
-- You are entering values on sheet1 and from cell T2
than try this.....

--Create a helper column on sheet1 (say on col. AA from cell AA2), which you may hide.
on cell AA2 use the below formula and copy it down

AA2 =IF(T2<>"",NOT(ISNUMBER(MATCH(T2,Sheet2!$A$2:$A$10,0))))

Now select the T2:"as far as needed" and make a new rule for conditional formatting using the following formula......

=AA2=TRUE

and set the format as per your requirement.

For detail, pls find the attached sheet.

Hope that helps.

Thanks for the feedback.