Highlighting duplicates across 8 work sheets

If you have a CF that works on one sheet, you can copy the range with the CF and pastspecial formats to the other sheet.

You can also use the Format Painter for this.

There seems to be some confusion,
1st
"...I tried the format painter but it only compares the values on each sheet.

2nd

"How would I write that formula so it covered the same cell range on all 8 sheets, the sheets are names 1,2,3,4,5,6,7 and 8? "

It sounds like we could use a small sample workbook (not picture or screenshot) representative of what you are trying to do.

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

As modeled on 'Sheet1', if you want to do a quick check for duplicates you could:
1) add another sheet,
2) populate row one with the original sheet names,
3) populate a range using: =INDIRECT(A$1&"!A"&ROW())
4) apply conditional formatting to that range using: =IF(A2=0,FALSE,COUNTIFS($A$2:$G$13,A2)>1)
Let us know if you have any questions.