VBA Excel 对比两列数据

浏览数：21 / 时间：2015年06月08日

Sub Md()
‘
‘ Macro1 Macro
‘ 宏由 BX 录制，时间: 2012-6-8
‘ 宏中的列数可以输入 A - IV 也可以输入 1-256
‘
Dim i%, j%, i1%, j1%, i2%, j2%
myi = UCase(InputBox("第一列"))
myj = UCase(InputBox("第二列"))
‘
‘************************************

If myi Like "[A-Z]" Then
i = Asc(myi) - 64
ElseIf myi Like "[A-I][A-Z]" Then
i = (Asc(Left(myi, 1)) - 64) * 26 + Asc(Right(myi, 1)) - 64
ElseIf IsNumeric(myi) Then
i = myi
Else
i = 1
End If
If myj Like "[A-Z]" Then
j = Asc(myj) - 64
ElseIf myj Like "[A-I][A-Z]" Then
j = (Asc(Left(myj, 1)) - 64) * 26 + Asc(Right(myj, 1)) - 64
ElseIf IsNumeric(myj) Then
j = myj
Else
j = 2
End If

‘ucase 转大写 upper lcase 转小写 lower

i2 = 40      ‘i列颜色
j2 = 40      ‘j列颜色

‘在上面更改哪两列对比,对比相同后的背景色
‘************************************
‘以下内容不用修改
Application.ScreenUpdating = False
If i = j Then j = i + 1
For i1 = 1 To Cells(30000, i).End(3).Row
For j1 = 1 To Cells(30000, j).End(3).Row
If Cells(i1, i).Value = "" Then Exit For
If Cells(j1, j).Interior.ColorIndex <> 40 And Cells(i1, i).Value = Cells(j1, j).Value Then
Cells(i1, i).Interior.ColorIndex = i2
Cells(j1, j).Interior.ColorIndex = j2
Exit For
End If
Next
Next
Application.ScreenUpdating = True
MsgBox "对比完成！" & vbCrLf & "刚才对比的是 " & i & " 列和 " & j & " 列的数据"
‘ 如果不想弹出提示 把上面前面加上 ‘ 英文单引号即可
End Sub