VBA to Locate Duplicate value on two ranges of cells for the entire workbook.

Hello
I have a workbook constructed of a consecutive 31 sheets each sheet represents a day of the month (January 1 , January 2 , ect )
Each day has two ranges of cells with value entered in on a daily basis. Range 1 is (H32:H41) and range 2 is (W3:W17)
-I was able to do a conditional format to compare between both ranges and find out duplicate values per each day.
but I need help to find a way to find out duplicate values entered on any cell of both ranges for the entire workbook.

Simply a warning prompt (if value was entered in January 5th already was entered on January 3rd)

Thank you.

Hello,

you can try the VBA method Range.Find …

Hello Marc
Would be too much to ask for the complete code ?
happy New Year

Thank you PCI , here is what i got
i got this prompt Attachment 854077
when i hit ok, it says Sheet 1 and sheet 2, hit ok again and goes on sheet 1 and sheet 3, and the loop goes on.
and it freezes any tasks on the workbook.

Thanks again for trying.
Happy New year

If I understood correctly, insert a macro like this into the ThisWorkbook module:

Please Login or Register  to view this content.

It will work automatically when you change the values ​​in the cells of the given ranges.

Hello
Sorry PCI it took me some time to respond back to you, the only downside to this macro is I have to hit the okay button to loop and compare comparing each sheet throughout the entire workbook.
the more I think about it, the more the conditional format way makes more sense to me and the way to go, but i hope Microsoft come up with a way that allow conditional format to work on the entire workbook than just a single sheet.

Thank you

Dear PCI
yes, Color Cells is preferred. Currently each sheet is conditionally formatting to itself to monitor both ranges within that sheet.

Thank you Again.

Hello PCI
It worked. but once I execute it for the first time, after that it will not recognize if there a duplicate value (Red color will not change from the cells on the remaining days that has duplicate value after the initial run).
for example, if I ran it on the 15th of the month for the first time. and try to run it again on the 19th day the red color remains on the cell that contain duplicate values from the 16th thru the 31st sheets.
and will also be very helpful at the end of the search if a pop-up message summarize which sheet names has duplicate value in those two ranges.
and lastly which I did not mention can you have the macro to run for the first 31 sheets only. I missed saying that the workbook has 37 sheets.

Thank you. your help is very well appreciated.

Different method
Place the code onto ThisWorkbook code module.
It runs each time you change any cell in that range in any sheet.

Please Login or Register  to view this content.