Conditional format or what to highlight cells matching cells in another sheet

[adhide]

This is probably easy, but man I cant work this out.

Simply put, I have a column that has 14,000 numbers (the data) on one sheet, and in another sheet I have 5,000 number (reference data)

How can I use conditional formating to highlight a row if that rows data is matched to a number in the reference sheet?

Im obviously using conditional formatting wrong or something cos I cant get it!!!!!!!!

[:) Sixthsense :)]

Use countif in CF formula as an example like the below

Please Login or Register  to view this content.

If you don't know how then

Please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).

[adhide]

For some reason I cant get the Countif to work, must be me.

A sheet is attached as an example.

[Tony Valko]

What version of Excel are you using?

In Excel versions 2007 and earlier you can't directly refer to another sheet in conditional formatting.

[adhide]

Excel 2010 and 2013

Is it best to put a hidden column in and do and if statement or similar!

[Tony Valko]

Try this...

Select the *entire* range A4:E14 starting from cell A4.
Cell A4 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

Use this formula as the CF rule:

=COUNTIFS(Match!$A$2:$A$12,$A4,Match!$C$2:$C$12,$C4)