Apply conditional formating criteria in entire workbook (not just in a single sheet)

My goal is to eliminate duplicate value not only in sheet1, but to include values of she开发者_StackOverflowet2 and sheet3 for example! So when I type a value which is already existing in sheet1 (for example I'm working in sheet2), to activate the condition of duplicating.

I get this message error when I try to select more than one sheet in any conditional formatting rule:

you cannot use references to other worksheets or workbooks for conditional formatting criteria

You'll have to code an event like Worksheet_Change to check for the duplication.

Despite you cannot use references to other worksheets or workbooks for conditional formatting criteria a named range of workbook scope that resides in one sheet is accessible in another in the same book.

So if you have say a, b and c in A1:A3 in Sheet1 and in Sheet2 and name Sheet1!A1:A3 Range1 then you can conditionally format Sheet2 A1:A3 by applying a formula such as:

=COUNTIF(Range1,A1)>0