Using Conditional Formatting To Detect Changes

I have a spreadsheet that I will send to multiple people to make changes to and I wanted to use Conditional Formatting to highlight the changes. I want to avoid using Macros.

My idea was to have my data to be updated on Sheet1 and have the original data on Sheet2, then do the following conditional formatting on Sheet1 to have the cells that differ between the two sheets highlight in yellow.

=INDIRECT("Sheet1!"&SUBSTITUTE(CELL("address"),"$",""))<>INDIRECT("Sheet2!"&SUBSTITUTE(CELL("address"),"$",""))

Example of what this cell should be is:

=INDIRECT("Sheet1!A1")<>INDIRECT("Sheet2!A1")

So if cells A1 in the 2 sheets are different then it will highlight yellow in Sheet1.

This formula is not working, when I change the <> to =, it highlights all the cells. Any help?

Hi Bernie,

When I try your formula, it gives me the following error:

"You cannot use references to other worksheets or workbooks for Conditional Formatting criteria."

Also, I was avoiding hardcoding cells because my spreadsheet has about 250 columns and 5000 rows and I want any cell that is changed to be highlighted yellow.

Thanks.