How to compare two worksheets and generate difference report as 3rd sheet?

[BuckeyeRJB]

I am having an issue with some of my vba code. I am trying to have two reports compare themselves. If there are differences it will highlight that cell red if its negative and green if its positive on sheet2. On the difference report (sheet3) it will show the difference value with its respected color as well. Sheet2 - Sheet1 would be the difference shown on sheet3.
If there is no difference it will display 0 for numeric values. Text and dates will stay the same if there is no difference.
I have completed this task in full except I only have it to where it will work if the data and reports match up with cells. I need it to have the capability to realize if the data starts in cell A15 on sheet1, and if sheet2's data would start at A17, I need it to know to not start at A15 on sheet2 but to start the comparison at A17. So A15 on sheet1 would compare itself to A17 on sheet2 and so on and so forth for the entire report.
When I run this now it would break it or sense everything is different if the reports don't match up. I need it to have a smart sense I guess and know that it needs to compare the correct data even if the cells don't match up. I have done tons of research and don't know if I have to use vlookup, match, index, or what? If so I don't even know where to begin. Code will be below.


<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Option Explicit
'This is where the program calls all sub procedures In Order.
Sub RunCompareSchedules()
Application.ScreenUpdating = False
Sheet3Creation "Sheet1", "Sheet2", "Sheet3"
Copy_range "Sheet1", "Sheet2", "Sheet3"
compareSheets "Sheet1", "Sheet2", "Sheet3"
DataPush "Sheet1", "Sheet2", "Sheet3"
CellFormat "Sheet1", "Sheet2", "Sheet3"
AutoFit "Sheet1", "Sheet2", "Sheet3"
Application.ScreenUpdating = True
End Sub


Sub compareSheets(shtSheet1 As String, shtSheet2 As String, shtSheet3 As String)

Dim mycell As Range
Dim mydiffs As Integer


'For each cell in sheet2 that is less in Sheet1, color it red, if it's more color it Green. If neither of these are true that don't add interior color.
For Each mycell In ActiveWorkbook.Worksheets(shtSheet2).UsedRange
If Not IsDate(mycell.Value) Or Not IsNumeric(mycell.Value) Then
If mycell.Value <> ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value Then
mycell.Interior.ColorIndex = 33
mydiffs = mydiffs + 1
Else
mycell.Interior.ColorIndex = 0
End If
End If
Next


For Each mycell In ActiveWorkbook.Worksheets(shtSheet2).UsedRange
If IsNumeric(mycell.Value) Then
If mycell.Value > ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value Then
mycell.Interior.Color = vbRed
mydiffs = mydiffs
ElseIf mycell.Value < ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value Then
mycell.Interior.Color = vbGreen
Else
mycell.Interior.ColorIndex = 0
End If
End If
Next


'For each cell in the date colomn sheet2 that is not the same in Sheet1, color it yellow
For Each mycell In ActiveWorkbook.Worksheets(shtSheet2).UsedRange
If IsDate(mycell.Value) Then
If mycell.Value < ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value Then
mycell.Interior.Color = vbGreen
mydiffs = mydiffs
ElseIf mycell.Value > ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value Then
mycell.Interior.Color = vbRed
Else
mycell.Interior.ColorIndex = 0
End If
End If
Next

If Sheets(shtSheet2).Cells(1, 1).Value <> Sheets(shtSheet1).Cells(1, 1).Value Then
Sheets(shtSheet2).Cells(1, 1).Interior.Color = vbYellow
mydiffs = mydiffs + 1
Else
Sheets(shtSheet2).Cells(1, 1).Interior.ColorIndex = 0
End If



If Sheets(shtSheet3).Cells(1, 1).Value <> Sheets(shtSheet1).Cells(1, 1).Value Then
Sheets(shtSheet3).Cells(1, 1).Interior.Color = vbYellow
Else
Sheets(shtSheet3).Cells(1, 1).Interior.ColorIndex = 0
End If


'Display a message box to demonstrate the differences
MsgBox mydiffs & " differences found. If Date cells are highlighted yellow on Sheet3, they will show the amount of difference in days.", vbInformation
ActiveWorkbook.Sheets(shtSheet2).Select

End Sub
Sub Copy_range(shtSheet1 As String, shtSheet2 As String, shtSheet3 As String)

'Copy worksheet 2 to worksheet 3
Worksheets("Sheet2").UsedRange.Copy
Worksheets("Sheet3").UsedRange.PasteSpecial

End Sub

Sub DataPush(shtSheet1 As String, shtSheet2 As String, shtSheet3 As String)

Dim mycell As Range
Dim mydiffs As Integer
Dim cellLoc As String


'For each cell in sheet3 that is not the same in Sheet2, color it red
For Each mycell In ActiveWorkbook.Worksheets(shtSheet3).UsedRange
If Not IsDate(mycell.Value) Or Not IsNumeric(mycell.Value) Then
If mycell.Value <> ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value Then
mycell.Interior.ColorIndex = 33
mydiffs = mydiffs + 1
Else
mycell.Interior.ColorIndex = 0
End If
End If
Next


For Each mycell In ActiveWorkbook.Worksheets(shtSheet3).UsedRange
If IsNumeric(mycell.Value) Then
If mycell.Value > ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value Then
mycell.Interior.Color = vbRed
mydiffs = mydiffs
ElseIf mycell.Value < ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value Then
mycell.Interior.Color = vbGreen
Else
mycell.Interior.ColorIndex = 0
End If
End If
Next


'For each cell in the date colomn sheet3 that is not the same in Sheet2, color it yellow
For Each mycell In ActiveWorkbook.Worksheets(shtSheet3).UsedRange
If IsDate(mycell.Value) Then
If mycell.Value < ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value Then
mycell.Interior.Color = vbGreen
ElseIf mycell.Value > ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value Then
mycell.Interior.Color = vbRed
Else
mycell.Interior.ColorIndex = 0
End If
End If
Next


'This will show the difference between each cell with a numeric value from sheet1 and 2, in sheet3. If it's not different, it will show a zero.
For Each mycell In Sheets(shtSheet3).UsedRange
If IsNumeric(mycell.Value) Then
If Not mycell.Value = Sheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value Then
ActiveWorkbook.Worksheets(shtSheet3).Cells(mycell.Row, mycell.Column).Value = _
ActiveWorkbook.Worksheets(shtSheet2).Cells(mycell.Row, mycell.Column).Value - ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value
ElseIf mycell.Value = "" Then
ActiveWorkbook.Worksheets(shtSheet3).Cells(mycell.Row, mycell.Column).Value = ""
Else
ActiveWorkbook.Worksheets(shtSheet3).Cells(mycell.Row, mycell.Column).Value = 0
End If
End If
Next

End Sub

Public Sub CellFormat(shtSheet1 As String, shtSheet2 As String, shtSheet3 As String)


Dim mycell As Range

'This will show the difference of dates, in days, from sheet1 and 2, in sheet3. If it's not different it will still show the date.
For Each mycell In Sheets(shtSheet3).UsedRange
If IsDate(mycell.Value) Then
If Not mycell.Value = Sheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value Then
ActiveWorkbook.Worksheets(shtSheet3).Cells(mycell.Row, mycell.Column).Value = _
ActiveWorkbook.Worksheets(shtSheet2).Cells(mycell.Row, mycell.Column).Value - ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value
End If
End If
Next


'This will format the cells in the date column to be in the General format if the cell is yellow.
For Each mycell In Sheets(shtSheet3).UsedRange
If IsDate(mycell.Value) Then
If mycell.Value <> ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value Then
ActiveWorkbook.Worksheets(shtSheet3).Cells(mycell.Row, mycell.Column).NumberFormat = "#,##0"
ElseIf mycell.Value = ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value Then
ActiveWorkbook.Worksheets(shtSheet3).Cells(mycell.Row, mycell.Column).NumberFormat = "m/d/yyyy"
End If
End If
Next
End Sub

Sub Sheet3Creation(shtSheet1 As String, shtSheet2 As String, shtSheet3 As String)


Dim shName As String, Wsh As Worksheet
shName = "Sheet3"


'This will loop through existing sheets to see if there is a sheet named "Sheet3". If there is a "Sheet3", then a message box will appear to
'let the user know that "Sheet3" already exists. If not it will exit loop and go to next area where it will create "Sheet3" at the end of
'excel sheets 1 and 2.
For Each Wsh In Sheets
If Wsh.Name = shName Then
If MsgBox("" & shName & " already exists! Please press Yes to continue or No to cancel operation.", vbYesNo) = vbNo Then
End
End If
Exit Sub 'Exit sub will allow the entire sub procedure to end if the "For If" Loop is true. If it's not true it will continue on.
End If
Next

'This section will create a worksheet called "Sheet3" if the "For If" loop above is false.
Set Wsh = ThisWorkbook.Sheets.Add(After:= _
ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
Wsh.Name = shName

End Sub

Sub AutoFit(shtSheet1 As String, shtSheet2 As String, shtSheet3 As String)

ActiveWorkbook.Worksheets(shtSheet1).UsedRange.Columns.AutoFit
ActiveWorkbook.Worksheets(shtSheet2).UsedRange.Columns.AutoFit
ActiveWorkbook.Worksheets(shtSheet3).UsedRange.Columns.AutoFit

End Sub</code>

 

[AlphaFrog]

There is no way to determine it ever.

Do you want the macro prompt the user to select the 1st data point on each sheet?

 

[BuckeyeRJB]

That is correct. Because everyone will be pulling different reports all the time. Each report is different. So yes, there is no way to know. Can I still do this? I mean i could make a new button for each type of report but that would entail approximately 40 buttons. at that point it would be defeating the purpose. because im sure that some of these users would know which macro to use for the report they pulled.

 

[BuckeyeRJB]

No i do not. I want it to be very easy for them. They should only have to hit the button and everything else happens for them. I don't want them to have to choose anything.

 

[AlphaFrog]

No i do not. I want it to be very easy for them. They should only have to hit the button and everything else happens for them. I don't want them to have to choose anything.

We seem to be going in circles. You say; "There is no way to determine it ever." Then ask for a macro that requires the 1st data point to be determined.

 

[AlphaFrog]

This example code returns the 1st cell in sheet 1 column A that matches any cell in column A on sheet 2.

    [color=darkblue]Dim[/color] v1 [color=darkblue]As[/color] [color=darkblue]Variant[/color], v2 [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color], j [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    
    [color=darkblue]With[/color] Sheets("Sheet1")
        v1 = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value
    End [color=darkblue]With[/color]
    
    [color=darkblue]With[/color] Sheets("Sheet2")
        v2 = .Range("A1", .Range("A" & Rows.Count).[color=darkblue]End[/color](xlUp)).Value
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] [color=darkblue]UBound[/color](v1, 1)
        [color=darkblue]If[/color] v1(i, 1) <> "" [color=darkblue]Then[/color]
            j = Application.Match(v1(i, 1), v2, 0)
            [color=darkblue]If[/color] IsNumeric(j) [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]For[/color]
        End [color=darkblue]If[/color]
    [color=darkblue]Next[/color] i
    
    [color=darkblue]If[/color] IsNumeric(j) [color=darkblue]Then[/color]
        MsgBox "Sheet1 A" & i & vbLf & _
               "Sheet2 A" & j, , "The First Matching Cells"
    [color=darkblue]Else[/color]
        MsgBox "No data match found from Sheet1 on Sheet2."
    End [color=darkblue]If[/color]

 

[mrmmickle1]

If you are using MS Excel 2010 or 2013 you could use Microsoft's spreadsheet compare?

2010: https://support.office.com/en-nz/ar...-Inquire-ebaf3d62-2af5-4cb1-af7d-e958cc5fad42

2013: https://support.office.com/en-ca/article/Basic-tasks-in-Spreadsheet-Compare-f2b20af8-a6d3-4780-8011-f15b3229f5d8

This functionality will compare all differences in two spreadsheets.

What's really nice is it has the functionality to compare differences in code modules....