在excel中，用VBA实现两列数据的比较

Question

如果我在A列中，输入的数据与B列中已有的数据有重复，那么A列中我输入的那个数据就变成红色，否则不变色，用VBA实现
qgrmdtj好象有些问题

Answer 1

实现代码如下：

1234567891011121314151617Sub abc() Dim D As Object, i As Integer, index As Integer Set D = CreateObject("scripting.dictionary") With Sheet1 For i = 1 To Range("b65536").End(xlUp).Row D(.Cells(i, 2).Value) = "" Next For i = 1 To Range("a65536").End(xlUp).Row If Not D.Exists(.Cells(i, 1).Value) Then index = index + 1 .Cells(index, 3) = .Cells(i, 1) End If Next End WithEnd Sub
用字典比较方便，省去重复的循环过程
如果数据量大，双层循环效率是很低的。

Answer 2

正面这段程序是反复测试过的，你粘贴到你的工作表的VBA中去就行了。

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 Then
If Target.Column = 1 Then
If Application.WorksheetFunction.CountIf(Range("B:B"), Target) >= 1 Then
Target.Interior.ColorIndex = 3
Else
Target.Interior.ColorIndex = -4142
End If
End If
End If
End Sub

Answer 3

用我这个试试

那就加一句

Private Sub Worksheet_Change(ByVal my As Range)
On Error Resume Next
With Application.WorksheetFunction
If my.Column = 1 Then
For Each rng In Range([a2], [a65536].End(xlUp))
If rng <> "" And .CountIf(Range([b2], [b65536].End(xlUp)), rng) > 0 Then
rng.Font.ColorIndex = 3
End If
Next
End If
End With
End Sub
------------------------------------------

那就加一句

Private Sub Worksheet_Change(ByVal my As Range)
On Error Resume Next
With Application.WorksheetFunction
If my.Column = 1 Then
For Each rng In Range([a2], [a65536].End(xlUp))
If rng <> "" And .CountIf(Range([b2], [b65536].End(xlUp)), rng) > 0 Then
rng.Font.ColorIndex = 3
Else
rng.Font.ColorIndex = 1
End If
Next
End If
End With
End Sub

Answer 4

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Font.ColorIndex = 0
Dim i As Integer
If Target.Column = 1 Then
For i = 1 To Range("b65536").End(xlUp).Row
If Target = Cells(i, 2) Then
Target.Font.ColorIndex = 3
Exit Sub
End If
Next
End If
End Sub

Answer 5

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Font.ColorIndex = 0
If Target.Column = 1 And Target = Target.Offset(0, 1) Then
Target.Font.ColorIndex = 3
End If
End Sub