实现代码如下：
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
用字典比较方便，省去重复的循环过程
如果数据量大，双层循环效率是很低的。
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
用字典比较方便，省去重复的循环过程
如果数据量大，双层循环效率是很低的。
温馨提示：内容为网友见解，仅供参考
第1个回答 2008-10-28
正面这段程序是反复测试过的，你粘贴到你的工作表的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
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
第2个回答 2008-10-28
用我这个试试
那就加一句
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本回答被提问者采纳
那就加一句
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本回答被提问者采纳
第3个回答 2008-10-28
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
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
第4个回答 2008-10-28
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
Target.Font.ColorIndex = 0
If Target.Column = 1 And Target = Target.Offset(0, 1) Then
Target.Font.ColorIndex = 3
End If
End Sub
相似回答
