Hi Catalin,

Thanks for sharing your thoughts.

As I mentioned, the code I provided is comparing two hard coded named ranges on the same sheet. Can you assist in adapting it (or use your code) to compare every named range on all sheets from both workbooks with the following assumptions?

1. Named Range exists in both files
1.a: Compare all cells for every named range even if they are not equal between the 2 files
1.b.1: for all cells in the range that are merged, highlight the first cell of the merged cells.

I am also able to perform the required actions using a conditional formatting using the following formula, however, this works on the whole sheet not specific named range as I wish it can do

="COUNTIF('Sheet1 (2)'!$A$1:$X$72,A1)=0"

Thanks for your patience and efforts.

Try the file attached.

It will be extremely slow, I personally quit running the code (after processing over 6000 cells in more than 30 minutes), there are lots of cells to be compared: many of them are processed more than once, the same cells are part of more than 1 named range. There is no way to speed it up.

Formatting cell by cell is slow, any code that does cell by cell operations is slow.

Just as a personal opinion, everything about what you are trying to do is not right, and this is the source of your problems that generates the need of complex solutions to solve simple problems.

Data entry/visualization and data storage are not "friends": users need a visually friendly format, which is usually NOT suitable for data storage and automation/reports. On the other side, tabular structures are not clear and user friendly, and there will always be a conflict between these needs.

In other words, typing data into the same place where you intend to store data is not right, it's like eating on the toilet seat. Data entry must be separated from data storage and reports.

My advice: redesign the process completely: use a userform to collect data from users, store weekly data in tabular structure in the same sheet, not in separate files. You will be able to extract many more information and reports from the stored data than you are able with the current setup.

Hi Catlin,

I really appreciate your efforts and advice and will try to optimize the inherited files.

Many Thanks!