Compare cells including formatting

[rlaemmler]

Hi,

I need to compare 2 worksheets for cell differences using VBA. Beside
value comparison I also need to detect format differences. So far I
couldn't find any better way then comparing cell by cell and format
property by format property (see example below). This comparison works
find but is slow especially for large spreadsheets.

Does anybody know a more powerful way doing this?

Example:
-------------
With cell
If (.Value <> originalWS.Cells(row, col).Value Or _
.Interior.color <> originalWS.Cells(row,
col).Interior.color Or _
.Interior.Pattern <> originalWS.Cells(row,
col).Interior.Pattern Or _
.Interior.PatternColor <> originalWS.Cells(row,
col).Interior.PatternColor Or _
.Font.color <> originalWS.Cells(row,
col).Font.color Or _
.Font.size <> originalWS.Cells(row, col).Font.size
Or _
.Font.name <> originalWS.Cells(row, col).Font.name
Or _
.Font.Strikethrough <> originalWS.Cells(row,
col).Font.Strikethrough Or _
.Font.Superscript <> originalWS.Cells(row,
col).Font.Superscript Or _
.Font.Subscript <> originalWS.Cells(row,
col).Font.Subscript Or _
.Font.BOLD <> originalWS.Cells(row, col).Font.BOLD
Or _
.Font.ITALIC <> originalWS.Cells(row,
col).Font.ITALIC Or _
.Font.Underline <> originalWS.Cells(row,
col).Font.Underline Or _
.HorizontalAlignment <> originalWS.Cells(row,
col).HorizontalAlignment Or _
.VerticalAlignment <> originalWS.Cells(row,
col).VerticalAlignment Or _
.............................
................................


Cheers,
Reto
www.xcellery.com

 

[Mike Fogleman]

If what you want is to discover a difference and point it out for all the
world to see, then this is pretty much what you need to do. But, if you
intend to fix the differences to equal the original, then what you really
want is an exact copy of the original. Copy/Paste can be done in a flash. We
need to know your ultimate goal for comparing these two sheets in order to
show you the shortest path.
Mike F

 

[rlaemmler]

Mike,

Thanks for your reply. I want to discover if 2 cells are different and
not to equal them with the original. Currently I check for each
formatting property if they are different but these many comparisons
makes it slow for large spreadsheets with lots of cells. I was thinking
of using the SheetChange event but that one only gets fired if the
value changes. Formatting changes don't trigger that event.

Any faster comparison would be highly appreciated. Is there any formula
which could do that?

Reto
www.xcellery.com

 

[Mike Fogleman]

I can't think of anything you might do differently in this case. It is going
to be slow working with format properties. There are no formulas that can do
this, VB is it.

Mike F
PS. It has to be faster than using your eyeballs.