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

**sktneer** · 01-21-2014, 11:46 PM

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.

**hutch@edge.net** · 01-22-2014, 12:34 AM

Thanks for thw quick reply, guys. I went with benishiryo's solution using conditional formatting. It works best for me because often the data is copied and pasted into these cells, and also because the format of the sheet is pretty rigid, so I didn't want to add or hide any additional columns.

Works perfectly. Thanks for the help!

This thrread is solved!