Highlight duplicate values in multiple sheets

I need to highlight duplicate items in column A in multiple sheets in Excel but I don't need to highlight duplicate items with ***'y Ref "N/A" given in column B. File attached

Sheet 1
A B
Item ***'y Ref
4238R093 65
4238R094 66
4238R095 67
60MK250 N/A
4238R097 69
4238R098 70
4238R099 71
4238R100 72
50MK250 N/A
4238R102 74

Sheet 2
Item ***'y Ref.
60MK250 N/A
5238R094 81
4238R094 82
5238R096 83
5238R097 84
5238R098 85
4238R098 86
5238R100 87
5238R101 88
5238R102 89

Sheet 3
Item ***'y Ref.
6238R093 90
6238R094 91
50MK250 N/A
6238R096 93
6238R097 94
4238R098 95
6238R099 96
6238R100 97
60MK250 N/A
6238R102 99

Sheet 4
Item ***'y Ref.
7238R093 100
7238R094 101
7238R095 102
4238R098 103
7238R097 104
7238R098 105
4238R094 106
7238R100 107
50MK250 N/A
7238R102 109

I could only thing of getting all the cells data in one column, then counting the duplicates.

Please Login or Register  to view this content.

Looking forward to seeing the array experts solutions

If you would like one conditional formatting rule per sheet try the following for Sheet 1:

Formula:

Please Login or Register  to view this content.

For Sheet 2 the formula would need to change to read:

Formula:

Please Login or Register  to view this content.

Similar changes would be needed for sheets 3:4.
Let us know if you have any questions.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

You're Welcome and thank you for the feedback. I hope that you have a blessed day.