"dynamically" auditing between 2 worksheets?

[kageh]

Hello everyone,

Please see the code below that I found online. It basically looks for discrepancies between 2 spreadsheets line by line. So, if CELL A1 on Sheet1 doesn't match CELL A1 on Sheet 2; it is identified on another sheet that is created. My only challenge is that the rows on both sheets have to be directly related. For example;

The screen shot below is an example of what I want to audit. Between rows 1-6 in Book 1 the data is the same as Book 2. Then it changes in rows 7-10. Book 2 matches between rows 1-6 but rows 7-8 are different while 9-10 are the same as book 1. Before I continue, perhaps a little background might help with what I'm trying to accomplish. The data for book 1 was pulled on Monday and the data for book 2 was pulled on Thursday. Overall, I'm trying to figure out if there were any differences between the 2 lists. Because the data set is small, it's evident that RID - 2478, RN - HVDRes; increased by 3 while the other groups in the rows remained the same. With that, if I use my current code; everything after row 7 will populate in my audit.

Is there a way to "dynamically" audit my data set without having each row being directly related? My ideal outcome in this example would be; 1) RID 2478 increase by 3 on Book 2 and these are the ones that do not have a match in Book 1. RID 2530 and 3574 are the same from both lists.

Here is the screen shot of my current example;


AuditEx.jpg

Here is the code I'm currently using to audit the worksheets

Sub Compare2WorkSheets(ws1 As Worksheet, ws2 As Worksheet)


Dim ws1row As Long, ws2row As Long, ws1col As Integer, ws2col As Integer
Dim maxrow As Long, maxcol As Integer, colval1 As String, colval2 As String
Dim report As Workbook, difference As Long
Dim row As Long, col As Integer
Set report = Workbooks.Add

    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\55511\My Documents\Mine\RCUpdComp - Test2 - 20130718.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

With ws1.UsedRange
ws1row = .Rows.Count
ws1col = .Columns.Count
End With
With ws2.UsedRange
ws2row = .Rows.Count
ws2col = .Columns.Count
End With
maxrow = ws1row
maxcol = ws1col
If maxrow < ws2row Then maxrow = ws2row
If maxcol < ws2col Then maxcol = ws2col
difference = 0
For col = 1 To maxcol
      For row = 1 To maxrow
      colval1 = ""
      colval2 = ""
      colval1 = ws1.Cells(row, col).Formula
      colval2 = ws2.Cells(row, col).Formula
      If colval1 <> colval2 Then
     difference = difference + 1
     Cells(row, col).Formula = colval1 & "<> " & colval2
     Cells(row, col).Interior.Color = 255
     Cells(row, col).Font.ColorIndex = 2
     Cells(row, col).Font.Bold = True
      End If
Next row
Next col
Columns("A:B").ColumnWidth = 25
report.Saved = True
If difference = 0 Then
report.Close False
End If
Set report = Nothing
MsgBox difference & " cells contain different data! ", vbInformation, "Comparing Two Worksheets"


End Sub

Thanks in advance for your help.

[Debraj Roy]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

Please upload a sample EXCEL file also, for testing purpose..

[kageh]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

Please upload a sample EXCEL file also, for testing purpose..

Thanks, I modified my original post but I don't see an option to attach a file to my post? Just an image? Sorry, I'm a newb to the forum.