Formatting to Identify Accounts NOT Added to a Table

[bmsrmd]

My question deals with conditional formatting that I can't seem to get to work how I invisioned it. I have laid our my problem in the example file attached.

This file has 2 tables that allow it to function...

Table 2:
This is simply a table that is auto generated from a system and displays Account Names / Date Added.

Table 1:
This table is updated manually for any new account names that show up on Table 2. Once an account has been added, then it doesn't needed to be added to this table twice (although it will show up numerous times on Table 2).

My Problem:
How can I setup conditional formatting so it recognizes when an account number is on Table 2 but has NOT been added to Table 1 manually. I would like it to color that account name in RED to easily show that it has not been added to Table 1. From my example, you can see CCA is highlighted because it now shows up on Table 2 but has not been added to Table 1. I'd like to automate this..

[etaf]

=COUNTIF('Table 1'!$A$2:$A$5,A2)=0

for 2007 or 2010 excel version
Conditional Formatting

Highlight applicable range >>

=$A$2:$A$9


Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:


=COUNTIF('Table 1'!$A$2:$A$5,A2)=0


Format… [Number, Font, Border, Fill]
OK >> OK