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

Question

My goal is to eliminate duplicate value not only in sheet1, but to include values of sheet2 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"

Answer 1

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