my code is
Sub Comparison()
Dim nA As Long, nB As Long
Dim rc As Long, i As Long, j As Long
rc = Rows.Count
nA = Cells(rc, "A").End(xlUp).Row
nB = Cells(rc, "B").End(xlUp).Row
Range("A1:A" & nA).Copy Range("C1")
Range("B1:B" & nB).Copy Range("C" & nA + 1)
For i = 1 To nA + nB
If i <= nA Then
Cells(i, "D") = "A"
Else
Cells(i, "D") = "B"
End If
Next i
Range("C1:D" & nA + nB).Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1:A" & nA).Clear
Range("B1:B" & nB).Clear
j = 2
If Range("D1").Value = "A" Then
Cells(1, "A") = Cells(1, "C")
Else
Cells(1, "B") = Cells(1, "C")
End If
For i = 2 To nA + nB
If Cells(i, "C") = Cells(i - 1, "C") Then
j = j - 1
Range("A" & j & ":B" & j) = Cells(i, "C")
j = j + 1
Else
If Cells(i, "D").Value = "A" Then
Cells(j, "A") = Cells(i, "C")
Else
Cells(j, "B") = Cells(i, "C")
End If
j = j + 1
End If
Next i
' coloring Macro
'
' Keyboard Shortcut: Ctrl+c
'
Columns("A:B").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(A1))=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
End Sub
So this macros takes 2 list of digits, lines up the exact matches, and highlights differences or where there are blanks. But it sorts everything including A1 and B1, what would i include in there to leave A1 or B1 alone, or just all of row one?
Bookmarks