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]

Does the Sheet1 data always start at A15? If not, how is the 1st data point to be determined from whatever is above the data?

If yes, this will find the match of Sheet1 A15 on Sheet2 and return the row offset.

    [COLOR=darkblue]Dim[/COLOR] n [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    n = Application.Match(Sheet1.Range("A15").Value, Sheet2.Range("A:A"), 0)
    [COLOR=darkblue]If[/COLOR] IsNumeric(n) [COLOR=darkblue]Then[/COLOR]
        n = 15 - n  [COLOR=green]'Sheet1 data offset from Sheet2[/COLOR]
    [COLOR=darkblue]Else[/COLOR]
        MsgBox "Start of data on Sheet2 was not matched."
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]

Then use something like this to offset the cells.

If mycell.Value > ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row [COLOR=#ff0000]+ n[/COLOR], mycell.Column).Value Then

 

[BuckeyeRJB]

It does not always start in A15. It's completely random depending on what type of report is pulled.

 

[oldbrewer]

right table starts at row 12 column 11

ROW

COLUMN

12

11

4

5

6

7

8

9

a

b

c

01/04/2015

02/04/2015

03/04/2015

4

5

6

7

8

9

a

b

c

01/04/2015

02/04/2015

03/04/2015

problem statement

macro

compare the left table with the right table

given that right table can start anywhere

For j = 1 To 100

from cellG1 down and right

For k = 7 To 18

If Cells(j, k) = "" Then GoTo 50 Else GoTo 75

50 Next k

given - left table starts at C5

Next j

75 Cells(1, 1) = "right table starts at row " & j & " column " & k

given - left table starts with C5=4

Cells(3, 1) = j: Cells(3, 2) = k

End Sub

problem is therefore

find the first non blank cell in the right table

simple solution

YOU NOW HAVE A FEW WAYS TO DO THE COMPARISON

a macro to check every cell starting at g1

ANOTHER MACRO CAN NOW COMPARE c5 WITH K12

G1 is cells(1,7)

C6 WITH k13 ETC ETC

R1 is cells(1,18)

HOW DO YOU WANT TO PROCEED

macro output to be placed in cell A1 for ease of understanding

row and column numbers repeated in A3 and B3

<colgroup><col span="2"><col span="3"><col span="5"><col><col><col><col span="5"></colgroup><tbody>
</tbody>

 

[oldbrewer]

Easy to put each cell on sheet3 leaving the text black if no difference and turn it red if there is a difference....

Or to only put different cells on sheet 3 but that would be hard to understand if there are only one or 2 errors

 

[AlphaFrog]

It does not always start in A15. It's completely random depending on what type of report is pulled.

Then how is the 1st data point on sheet1 determined? Is it the 1st used cell in column A. Is there non-data (headers) above the 1st data point?

 

[BuckeyeRJB]

There are non data cells in the top of these reports that need to be ignored. It needs to be able to know where to start comparison even if the first data cells in sheet1 start at A15 and the data cells start at A17 in sheet2. The headers and reports depending on what filters are used changes the aspect and structure of the reports.

 

[AlphaFrog]

There are non data cells in the top of these reports that need to be ignored. It needs to be able to know where to start comparison even if the first data cells in sheet1 start at A15 and the data cells start at A17 in sheet2. The headers and reports depending on what filters are used changes the aspect and structure of the reports.

My question is about Sheet1 only. What criteria is to be used to determine the first data cell from the non-data above it? Once the 1st data point on Sheet1 is determined, finding its match on Sheet2 should be easy.

 

[BuckeyeRJB]

Well thats the issue. There is no way to determine it ever. The reports differ all the time. We have production reports that have about 25 different ways it can look, schedule reports that have a few different types. The thing is, I wouldn't be working with it. I would give the tool to others and let them use it for when they will need to pull reports and compare them. But the reports they will pull and use this tool for, it would differ all the time depending on what the individual was trying to pull.

 

[oldbrewer]

There is no way to determine it ever.

that is your answer then.