If Value is found on one sheet then highlight on another sheet of same workbook

[prefcomm]

Hi All, I think this is a simple one that I am overlooking. See attached workbook.

If the serial number is already on EITHER Sheet 1 OR Sheet 2, then when I enter it on Sheet 3 I want it to automatically highlight it on Sheet 1 or Sheet 2 (whichever it may be located).

I think this would use Conditional Formatting but cannot figure it out.

I appreciate your help!!
-prefcomm

[Bernie Deitrick]

Use the formula option - select cell A2: last cell on the first sheet, and use the formula

=(SUMPRODUCT((A2<>"")*(A2='Sheet 2'!$A$2:$O$13)*1)+SUMPRODUCT((A2<>"")*(A2='Sheet 3'!$A$2:$O$13)*1))>0

and similar for the other two sheets

If you have more than the 12 rows of values, increase the ranges to match.

Note that you could format those cells as text, and not use

="12345678901234567890"
and just use this:
12345678901234567890

[prefcomm]

Hi Bernie, I am unsure where to insert this formula. I usually use formulas like IF, SUM, VLOOKUP, etc and they are inserted within a specific cell. Where do I insert this formula?

[Bernie Deitrick]

Conditional Formatting has an option to use a formula to determine when a cell should be formatted. So select your cells on Sheet 1, from A2 through the end, and select Conditional Formatting, the formula option, and insert the formula into the formula box and choose your formatting. If you have an issue, I can upload a working version.

[prefcomm]

Thanks Bernie! Question - should I do this on Sheet 3 only so that it looks at Sheet 1 and Sheet 2 to see if the serial number is found?

When I choose A2:O15 (on ANY of the Sheets) and then do the Conditional Formatting Button and use the "Use a formula..." and insert the formula you have listed here, I get a message that says "You cannot use references to other worksheets or workbooks for Conditional Formatting criteria". Am I missing a step here? Sorry for the inconvenience and thanks again!

-prefcomm

[Bernie Deitrick]

I'm using Excel 2016, which allows the cross-sheet reference to work. Here is my working version: