快速选中所有相同内容的单元格

快速选中所有相同内容的单元格

转自：https://zhuanlan.zhihu.com/p/61886749

 

 

我们利用SelectionChange时间，当我们选择单元格发生改变时，调用VBA自带的Find方法，去单元格区域是，查找相同的内容，并着色

'********************************************
'--功能:批量选中所有相同的单元格
'--日期:2019-4-9
'--作者:Excel办公实战-小易
'********************************************
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim firstRng As Range, firstAddress As String
    Dim resultRng As Range
    If Target.Cells.Count <> 1 Then
        Exit Sub
    End If
    
    With Range("A1").CurrentRegion
        .Interior.Color = xlNone
        Set firstRng = .Find(what:=Target.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not firstRng Is Nothing Then
            firstAddress = firstRng.Address
            Set resultRng = firstRng
            Do
                Set firstRng = .FindNext(firstRng)
                Set resultRng = Union(firstRng, resultRng)
            Loop While Not firstRng Is Nothing And firstAddress <> firstRng.Address
        End If
        If resultRng Is Nothing Then Exit Sub
        With resultRng
            .Interior.ColorIndex = 6
            .Select
        End With
    End With
End Sub

 

发布于 2019-04-09