COUNTIF command in Microsoft excel 2010

Standard excel document with 2 sheets in office 2010
How can 2 rows be compare and highlight the values, when the value shows in both list.

Sheet 1 | Sheet 2

A               A
1234         4324
2222         3338
3333         1234
4444         4556

Now I excel need to automatically highlight the value 1234 because it show in both rows.
Current I try to get this done by using the COUNTIF statement


As soon I hit enter I got an error message, the formula you typed in contains an error.
Patrick de GelderSupport engineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
I don't see anything wrong with the formula.

Where are you using the formula?
Patrick de GelderSupport engineerAuthor Commented:
Standard edition from Microsoft excel 2010,

First selected column A on sheet 2, then I go to the option conditional formatting, then fill in the formula and selected a specific color and as soon I click on it reports the above error.
NorieAnalyst Assistant Commented:
Does the formula work if you enter it on a worksheet?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Patrick de GelderSupport engineerAuthor Commented:
No have tried that any other statement seems to work fine
NorieAnalyst Assistant Commented:
I selected A2:A58 on Blad1, went to conditional formatting, selected Use a formula to determine which cells to format, entered this formula,

=COUNTIF(Blad2!A:A, A2)

and formatted for a red fill.

See the attached workbook for the results.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Patrick de GelderSupport engineerAuthor Commented:
Thanks, you help me a lot,
It's very strange it must be a problem with office I checked the formula in your sheet and it works well :). Then in a new empty document same formula it doesn't work.
But then there is no reason to troubleshoot this further as this pc is going soon anyway.

For further reference we confirmed that the formula works. :-)
Patrick de GelderSupport engineerAuthor Commented:
Thanks for helping get this problem fixed
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.