Comparing zip codes to keep in a column

[xtremekyter]

I have an excel spreadsheet with full addresses and also have a spreadsheet of zip codes to keep. I am trying to find an automated way to compare and highlight zip codes not on my list of keepers without having to do it manually as I have ove 11,000 records. I'm thinking it would be an "is not equal to" function but have never used that.Thanks in advance. Jeff

 

[xtremekyter]

Select Column F or the Column F range you need>
From Excel "Home" tab, Click "Conditional Formatting">
"New Rule">"Use a formula to determine which cells to format">
Enter my formula in Formula box>Click "Format">
Select "Fill", select fill color of your choice, click OK

I got conflicting results:
32068 was highlighted in one case and not highlight just below it. Same with 32073. I got one highlighted and one not.

 

[jtakw]

How did you select your Range exactly?

 

[xtremekyter]

How did you select your Range exactly?

Selected the header and the whole column. Then I tried it only selecting the cells in the column leaving the header out but got the same result.

 

[jtakw]

I should have been more specific about how to select your range...

=COUNTIF(H$2:H$15,F2)=0

1. Make sure your list of "Good zip codes" are in H$2:H$15 or Change this range in the formula accordingly, to wherever your list is on your sheet, make sure you use absolute referencing as shown.
2. You Must select your range Starting at F2, if your range starts at F1, then you can select the entire Column, change F2 in the formula accordingly to whatever cell your data starts.

 

[xtremekyter]

I should have been more specific about how to select your range...

=COUNTIF(H$2:H$15,F2)=0

1. Make sure your list of "Good zip codes" are in H$2:H$15 or Change this range in the formula accordingly, to wherever your list is on your sheet, make sure you use absolute referencing as shown.
2. You Must select your range Starting at F2, if your range starts at F1, then you can select the entire Column, change F2 in the formula accordingly to whatever cell your data starts.

It was my fault. I actually had 72 zip codes to keep but didn't alter the formula you provided to reflect that. It works perfectly. Thank you for the lesson. I am grateful. Jeff

 

[jtakw]

You're welcome, thanks for the feedback.