Dim newsheet
Dim sh As Worksheet
Dim rA As Range, rB As Range, rA2 As Range, rB2 As Range, rA3 As Range, rB3 As Range
Dim cellA As Range, cellB As Range
'Copy function start
LastSheetName = "Results"
OutSheet = "OutputSheet"
OrgFileName = ActiveWorkbook.Name
NameWorkbook = "Database_Compare.xlsm"
Workbooks(OrgFileName).Activate
Workbooks(OrgFileName).Worksheets(1).Visible = True
Workbooks(OrgFileName).Sheets(1).Activate
Workbooks(OrgFileName).Sheets(1).Select
Workbooks(OrgFileName).Sheets(1).Copy After:=Workbooks(NameWorkbook).Sheets(LastSheetName)
Sheets(3).Name = "Compared"
'Copy Function End
Set sh = ActiveSheet
Set rA = Worksheets(1).Range("A2", Worksheets(1).Cells(Worksheets(1).Rows.Count, "A").End(xlUp))
Set rB = Worksheets(3).Range("A2", Worksheets(3).Cells(Worksheets(3).Rows.Count, "A").End(xlUp))
Set rA2 = Worksheets(1).Range("B2", Worksheets(1).Cells(Worksheets(1).Rows.Count, "B").End(xlUp))
Set rB2 = Worksheets(3).Range("B2", Worksheets(3).Cells(Worksheets(3).Rows.Count, "B").End(xlUp))
Set rA3 = Worksheets(1).Range("C2", Worksheets(1).Cells(Worksheets(1).Rows.Count, "C").End(xlUp))
Set rB3 = Worksheets(3).Range("C2", Worksheets(3).Cells(Worksheets(3).Rows.Count, "C").End(xlUp))
rA.Interior.ColorIndex = xlNone
rB.Interior.ColorIndex = xlNone
'0 - Black, 1 - Blue, 2 - Green, 3 - Cyan, 4 - Red, 5 - Magenta, 6 - Yellow/Brown, 7 - White, 8 - Gray,
'9 - Bright Blue, A - Bright Green, B - Bright Cyan, C - Bright Red, D - Bright Magenta, E - Bright Yellow, F - Bright White
'--------------------------------------------------------------------------------
'Start of Column One Check
For Each cellB In rB
If Application.CountIf(rA, cellB) = 1 Then
cellB.Interior.ColorIndex = 6 ' Colors cells
End If
Next
For Each cellA In rA
If Application.CountIf(rB, cellA) = 1 Then
cellA.Interior.ColorIndex = 6 'Colors cells
'This code copys cells that matched and pastes them in results sheet but it skips matchs entirely
cellA.EntireRow.Copy
Sheets(2).Range("A65536").End(xlUp).Offset(2, 0).PasteSpecial
End If
Next
'End of Column One Check
'Start of Column Two Check
For Each cellB In rB2
If Application.CountIf(rA2, cellB) = 1 Then
cellB.Interior.ColorIndex = 3 'Colors cells
End If
Next
For Each cellA In rA2
If Application.CountIf(rB2, cellA) = 1 Then
cellA.Interior.ColorIndex = 3 'Colors cells
cellA.EntireRow.Copy
Sheets(2).Range("A65536").End(xlUp).Offset(2, 0).PasteSpecial
End If
Next
'End of Column Two Check
'Start of Column Three Check
For Each cellB In rB3
If Application.CountIf(rA3, cellB) = 1 Then
cellB.Interior.ColorIndex = 7 'Colors cells
End If
Next
For Each cellA In rA3
If Application.CountIf(rB3, cellA) = 1 Then
cellA.Interior.ColorIndex = 7 'Colors cells
cellA.EntireRow.Copy
Sheets(2).Range("A65536").End(xlUp).Offset(2, 0).PasteSpecial
End If
Next
'End of Column Three Check
Bookmarks