Find Discrepancies between Worksheets

I have 2 worksheets that should be identical (merged cells, colors/borders,
number formats...), but when I try to paste values from one to the other, it
kicks it out because merged cells are not identically sized. Obviously,
there is some discrepancy in the thousands of cells of formatting.

Is there a way to compare the 2 worksheets and come up with a list of what
is different?

It's a long story why I can't just overwrite the one, but please just trust
that one sheet cannot be changed at all and the other is an output from a
database that I do not have access to and so have to give the administrator a
list of changes to be made.

Thank you for any help!

Erin

Maybe you could use a program written by Myrna Larson and Bill Manville.

You can find a copy on Chip Pearson's site:
http://www.cpearson.com/excel/whatsnew.htm
look for compare.xla



Erin wrote:
>
> I have 2 worksheets that should be identical (merged cells, colors/borders,
> number formats...), but when I try to paste values from one to the other, it
> kicks it out because merged cells are not identically sized. Obviously,
> there is some discrepancy in the thousands of cells of formatting.
>
> Is there a way to compare the 2 worksheets and come up with a list of what
> is different?
>
> It's a long story why I can't just overwrite the one, but please just trust
> that one sheet cannot be changed at all and the other is an output from a
> database that I do not have access to and so have to give the administrator a
> list of changes to be made.
>
> Thank you for any help!
>
> Erin

--

Dave Peterson

Wow - very cool, but I still need more information. This only checks values,
formulas and number formats...I need more. I think I could get away with
this and a list of where merged cells don't line up, but colors/lines would
also be very helpful.

Thank you for your help! Believe me, I will keep this add-in handy!

Erin


"Dave Peterson" wrote:

> Maybe you could use a program written by Myrna Larson and Bill Manville.
>
> You can find a copy on Chip Pearson's site:
> http://www.cpearson.com/excel/whatsnew.htm
> look for compare.xla
>
>
>
> Erin wrote:
> >
> > I have 2 worksheets that should be identical (merged cells, colors/borders,
> > number formats...), but when I try to paste values from one to the other, it
> > kicks it out because merged cells are not identically sized. Obviously,
> > there is some discrepancy in the thousands of cells of formatting.
> >
> > Is there a way to compare the 2 worksheets and come up with a list of what
> > is different?
> >
> > It's a long story why I can't just overwrite the one, but please just trust
> > that one sheet cannot be changed at all and the other is an output from a
> > database that I do not have access to and so have to give the administrator a
> > list of changes to be made.
> >
> > Thank you for any help!
> >
> > Erin
>
> --
>
> Dave Peterson
>

You can go through each cell looking for borders, but there are lots of things
that could change--color, weight, style, position (top/bottom/left/right/both
inside diagonals). Way beyond my interest!

But to just check merged cells, you could use something like:

Option Explicit
Sub testme()

Dim rptWks As Worksheet
Dim wks As Worksheet
Dim oRow As Long
Dim oCol As Long
Dim iCtr As Long
Dim wksNames As Variant
Dim myCell As Range

wksNames = Array("sheet1", "sheet2")

Set rptWks = Worksheets.Add
rptWks.Range("a1").Resize(1, 2).Value = wksNames

oCol = 0
For iCtr = LBound(wksNames) To UBound(wksNames)
oCol = oCol + 1
oRow = 1
Set wks = Worksheets(wksNames(iCtr))
For Each myCell In wks.UsedRange.Cells
If myCell.MergeArea.Cells.Count > 1 Then
If myCell.MergeArea.Cells(1).Address = myCell.Address Then
oRow = oRow + 1
rptWks.Cells(oRow, oCol).Value = myCell.MergeArea.Address
End If
End If
Next myCell
Next iCtr

With rptWks
.Columns(2).Insert
.Range("b1").Value = "On: " & wksNames(UBound(wksNames))
.Range("d1").Value = "On: " & wksNames(LBound(wksNames))
.Range("B2:b" & .Cells(.Rows.Count, "A").End(xlUp).Row).Formula _
= "=isnumber(match(a2,c:c,0))"
.Range("d2:d" & .Cells(.Rows.Count, "c").End(xlUp).Row).Formula _
= "=isnumber(match(a2,a:a,0))"
End With

End Sub

Erin wrote:
>
> Wow - very cool, but I still need more information. This only checks values,
> formulas and number formats...I need more. I think I could get away with
> this and a list of where merged cells don't line up, but colors/lines would
> also be very helpful.
>
> Thank you for your help! Believe me, I will keep this add-in handy!
>
> Erin
>
> "Dave Peterson" wrote:
>
> > Maybe you could use a program written by Myrna Larson and Bill Manville.
> >
> > You can find a copy on Chip Pearson's site:
> > http://www.cpearson.com/excel/whatsnew.htm
> > look for compare.xla
> >
> >
> >
> > Erin wrote:
> > >
> > > I have 2 worksheets that should be identical (merged cells, colors/borders,
> > > number formats...), but when I try to paste values from one to the other, it
> > > kicks it out because merged cells are not identically sized. Obviously,
> > > there is some discrepancy in the thousands of cells of formatting.
> > >
> > > Is there a way to compare the 2 worksheets and come up with a list of what
> > > is different?
> > >
> > > It's a long story why I can't just overwrite the one, but please just trust
> > > that one sheet cannot be changed at all and the other is an output from a
> > > database that I do not have access to and so have to give the administrator a
> > > list of changes to be made.
> > >
> > > Thank you for any help!
> > >
> > > Erin
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson