| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare cells including formatting
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Compare cells including formatting
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 wrote in message oups.com... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Compare cells including formatting
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 wrote: 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 wrote in message oups.com... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Compare cells including formatting
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. wrote in message ups.com... 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 wrote: 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 wrote in message oups.com... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
| If statement with Vlookup - including ISNA function to compare two columns from different worksheets | Excel Worksheet Functions | |||
| Setting one cell = to another, INCLUDING FORMATTING. | Excel Discussion (Misc queries) | |||
| How do I compare cells and if FALSE compare to next cell in EXCEL | Excel Worksheet Functions | |||
| How to create a link including all formatting from origin cell? | Excel Worksheet Functions | |||
Linear Mode
