Excel - Compare two sheets

Sub VERIFY_ADDRESS()

Sheets("Sheet2").Select

Columns("A:A").Select

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 9), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)), TrailingMinusNumbers:=True

Dim lastrow As Long, lastrow1 As Long, lastrow2 As Long, lastrow4 As Long, erow As Long, i As Long, x As Long, y As Long, z As Long, j As Long

Dim myname As String, myname2 As String

lastrow = Sheet2.Cells(Rows.Count, 2).End(xlUp).Row

For i = 1 To lastrow

If Sheet2.Cells(i, 2) > 100000 Then

Sheet2.Cells(i, 1).Copy

erow = Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Sheet2.Paste Destination:=Worksheets("sheet3").Cells(erow, 1)

Sheet2.Cells(i, 2).Copy

Sheet2.Paste Destination:=Worksheets("sheet3").Cells(erow, 2)

Sheet2.Cells(i, 3).Copy

Sheet2.Paste Destination:=Worksheets("sheet3").Cells(erow, 3)

Sheet2.Cells(i, 4).Copy

Sheet2.Paste Destination:=Worksheets("sheet3").Cells(erow, 4)

Sheet2.Cells(i, 5).Copy

Sheet2.Paste Destination:=Worksheets("sheet3").Cells(erow, 5)

Sheet2.Cells(i, 6).Copy

Sheet2.Paste Destination:=Worksheets("sheet3").Cells(erow, 6)

Sheet2.Cells(i, 7).Copy

Sheet2.Paste Destination:=Worksheets("sheet3").Cells(erow, 7)

End If

Next i

Sheets("Sheet3").Select

Range("C2").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.TextToColumns Destination:=Range("C2"), DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(5, 1)), TrailingMinusNumbers:=True

Range("E2").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.TextToColumns Destination:=Range("E2"), DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(5, 1)), TrailingMinusNumbers:=True

Range("F2").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.TextToColumns Destination:=Range("F2"), DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(5, 1)), TrailingMinusNumbers:=True

Sheets("Sheet2").Select

Cells.Select

Range("A37").Activate

Selection.ClearContents

Sheets("Sheet1").Select

Columns("B:B").Select

Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

lastrow5 = Sheet1.Cells(Rows.Count, 2).End(xlUp).Row

For i = 2 To lastrow

If Sheet1.Cells(i, 2) > 100000 Then

Sheet1.Cells(i, 1).Copy

erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Sheet1.Paste Destination:=Worksheets("sheet2").Cells(erow, 1)

Sheet1.Cells(i, 2).Copy

Sheet1.Paste Destination:=Worksheets("sheet2").Cells(erow, 2)

Sheet1.Cells(i, 3).Copy

Sheet1.Paste Destination:=Worksheets("sheet2").Cells(erow, 3)

Sheet1.Cells(i, 4).Copy

Sheet1.Paste Destination:=Worksheets("sheet2").Cells(erow, 4)

Sheet1.Cells(i, 5).Copy

Sheet1.Paste Destination:=Worksheets("sheet2").Cells(erow, 5)

Sheet1.Cells(i, 6).Copy

Sheet1.Paste Destination:=Worksheets("sheet2").Cells(erow, 6)

Sheet1.Cells(i, 7).Copy

Sheet1.Paste Destination:=Worksheets("sheet2").Cells(erow, 7)

End If

Next i

Sheets("Sheet2").Select

Columns("B:B").Select

Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

Columns("C:C").Select

Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

If IsEmpty(Range("D1").Value) = True Then

Else

Columns("D:D").Select

Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

End If

Columns("E:E").Select

Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

Columns("F:F").Select

Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

Columns("G:G").Select

Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

Range("A1").Select

lastrow1 = Sheets("sheet2").Range("B" & Rows.Count).End(xlUp).Row

For x = 2 To lastrow1

myname = Sheets("sheet2").Cells(x, "B").Value

Sheets("sheet3").Activate

lastrow2 = Sheets("Sheet3").Range("B" & Rows.Count).End(xlUp).Row

For j = 2 To lastrow2

If Sheets("sheet3").Cells(j, "B").Value = myname Then

Sheets("sheet2").Activate

Sheets("sheet2").Range(Cells(x, "A"), Cells(x, "G")).Copy

Sheets("sheet3").Activate

Sheets("sheet3").Range(Cells(j, "H"), Cells(j, "N")).Select

ActiveSheet.Paste

End If

Next j

Application.CutCopyMode = False

Next x

Sheets("Sheet3").Select

lastrow6 = Sheet3.Cells(Rows.Count, 3).End(xlUp).Row

For i = 1 To lastrow6

Sheet3.Cells(i, 3) = Format(Sheet3.Cells(i, 3), ">")

Sheet3.Cells(i, 4) = Format(Sheet3.Cells(i, 4), ">")

Sheet3.Cells(i, 5) = Format(Sheet3.Cells(i, 5), ">")

Sheet3.Cells(i, 6) = Format(Sheet3.Cells(i, 6), ">")

Sheet3.Cells(i, 10) = Format(Sheet3.Cells(i, 10), ">")

Sheet3.Cells(i, 11) = Format(Sheet3.Cells(i, 11), ">")

Sheet3.Cells(i, 12) = Format(Sheet3.Cells(i, 12), ">")

Sheet3.Cells(i, 13) = Format(Sheet3.Cells(i, 13), ">")

Next i

Sheets("Sheet3").Select

lastrow4 = Sheet3.Cells(Rows.Count, 2).End(xlUp).Row

For y = 2 To lastrow4

If Sheet3.Cells(y, 2) <> Sheet3.Cells(y, 9) Then

Sheet3.Cells(y, 2).Select

With Selection.Font

.Color = -16776961

.TintAndShade = 0

End With

Sheet3.Cells(y, 9).Select

With Selection.Font

.Color = -16776961

.TintAndShade = 0

End With

End If

If Sheet3.Cells(y, 3) <> Sheet3.Cells(y, 10) Then

Sheet3.Cells(y, 3).Select

With Selection.Font

.Color = -16776961

.TintAndShade = 0

End With

Sheet3.Cells(y, 10).Select

With Selection.Font

.Color = -16776961

.TintAndShade = 0

End With

End If

If Sheet3.Cells(y, 4) <> Sheet3.Cells(y, 11) Then

Sheet3.Cells(y, 4).Select

With Selection.Font

.Color = -16776961

.TintAndShade = 0

End With

Sheet3.Cells(y, 11).Select

With Selection.Font

.Color = -16776961

.TintAndShade = 0

End With

End If

If Sheet3.Cells(y, 5) <> Sheet3.Cells(y, 12) Then

Sheet3.Cells(y, 5).Select

With Selection.Font

.Color = -16776961

.TintAndShade = 0

End With

Sheet3.Cells(y, 12).Select

With Selection.Font

.Color = -16776961

.TintAndShade = 0

End With

End If

If Sheet3.Cells(y, 6) <> Sheet3.Cells(y, 13) Then

Sheet3.Cells(y, 6).Select

With Selection.Font

.Color = -16776961

.TintAndShade = 0

End With

Sheet3.Cells(y, 13).Select

With Selection.Font

.Color = -16776961

.TintAndShade = 0

End With

End If

If Sheet3.Cells(y, 7) <> Sheet3.Cells(y, 14) Then

Sheet3.Cells(y, 7).Select

With Selection.Font

.Color = -16776961

.TintAndShade = 0

End With

Sheet3.Cells(y, 14).Select

With Selection.Font

.Color = -16776961

.TintAndShade = 0

End With

End If

Next y

Cells.Select

Cells.EntireColumn.AutoFit

Range("A1").Select

ThisWorkbook.Saved = True

MyDate = Format(Date, "mmddyyyy")

MyYear = Mid(MyDate, 5, 4)

MyMonth = Mid(MyDate, 1, 2)

MyDay = Mid(MyDate, 3, 2)

Dim myUser As String

myUser = Environ("username")

Dim TicNum As Variant

Do

TicNum = Application.InputBox("Enter the Ticket number.", Type:=1)

Loop While TicNum < 999999 Or TicNum > 9999999 Or TicNum = False

MyFile = "S:\FinAdj\Projects\" & MyYear & "\Asset\" & MyYear & MyMonth & MyDay & "_TIC" & TicNum & "_QA_AS_ADDR_UPDATE.xlsx"

ChDir "S:\FinAdj\Projects\" & MyYear & "\Asset\"

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:="S:\FinAdj\Projects\" & MyYear & "\Asset\" & MyYear & MyMonth & MyDay & "_TIC" & TicNum & "_QA_AS_ADDR_UPDATE.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

End Sub