Use Conditional formatting for whole Google Sheet Workbook to search for duplicates

Question

Use Conditional formatting for whole Google Sheet Workbook Currently I am using the single color CONDITIONAL FORMATTING for two columns in every worksheet that looks at the column and if there is a similar match with the single worksheet within that column it will highlight the column in yellow.

APPLY TO RANGE: C1:C1000

FORMAT RULES Format cells if ... =if(C1<>"",Countif(C:C,left(C1,18)& "*") > 1)

Formatting Style I selected yellow

And I do exactly the same thing for Column D

Now that I have over 10 worksheets and it is growing week by week I am finding that there are duplicates happening between the sheets, as conditional formatting only works per sheet.

So is there a way for me to do have Google Sheet conditionally format the entire workbook by looking at column C and column D and if there is a match that it highlights it in yellow.

If there is a script or macro that someone can actually code for me (as I don't know how to do it) I would really appreciate. Alternatively if anyone knows an easy way to do what I need done, I would be most grateful.

As an example here is a workbook to show you what I mean. In the google workbook, in Sheet labelled "Visual Test Current" this is what I have been able to currently setup as described. It works perfectly.

In the sheet labelled "Visual Test Sheet 1" it works fine on the individual sheet itself, however it does not look at "Visual Test Current" and if there is a match based on the formula "=if(D1<>"",Countif(D:D,left(D1,18)& "") > 1)" AND =if(E1<>"",Countif(E:E,left(E1,18)& "") > 1)

It does not format the cell to tell me that there is a duplicate match in a previous sheet. That is what I am trying to figure out how to do.

How do I get/create a formula that looks at these two columns through every sheet in Google Workbook and if there is a match based on the parameters of Countif it formats the cell to yellow, highlighting to me that there is a duplicate of that cell somewhere in a sheet in Google Workbook.

Here is the link to the shared Test document to help you visual what I am trying to do.

https://docs.google.com/spreadsheets/d/e/2PACX-1vQryGa9Z325XMv1mU5hq0AhaO3uWbzZ7MSxfY7U4EcPS7hGg281wogKJE98IQjriR4cPIXTCatd-Y8d/pubhtml