Excel VBA查找并高亮指定值单元格 解决Find方法部分匹配误高亮问题

原有VBA代码调用Find方法时未指定匹配规则，默认采用部分匹配模式，只要单元格内容包含目标值就会被判定为命中，因此内容为22、23的单元格也会被识别为匹配项。

只需要在Find方法的参数中添加LookAt:=xlWhole，即可强制要求完全匹配单元格的完整内容，仅命中值完全等于搜索目标的单元格。

如果你的搜索目标为数字，也可将Searchfor的变量类型修改为Variant或者Integer等数值类型，避免不同单元格格式带来的匹配异常。

'Find Search Values on Sheet and Highlight
Sub Find_And_Highlight()

Dim Searchfor As Variant ' 改为Variant兼容数值、文本格式的2都可匹配
Dim FirstFound As String
Dim Lastcell As Range
Dim FoundCell As Range
Dim rng As Range
Dim myRange As Range

Set myRange = ActiveSheet.UsedRange
Set Lastcell = myRange.Cells(myRange.Cells.Count)
      
Searchfor = 2 ' 搜索目标为数字2，去掉引号避免字符串匹配问题
        
' 新增LookAt:=xlWhole参数指定完全匹配
Set FoundCell = myRange.Find(what:=Searchfor, after:=Lastcell, LookAt:=xlWhole)
        
'Test to see if anything was found
If Not FoundCell Is Nothing Then
    FirstFound = FoundCell.Address
Else
    GoTo NothingFound
End If
         
Set rng = FoundCell
        
'Loop until cycled through all finds
Do Until FoundCell Is Nothing
    'Find next cell with Searchfor value
    Set FoundCell = myRange.FindNext(after:=FoundCell)
    'Add found cell to rng range variable
    Set rng = Union(rng, FoundCell)
    'Test to see if cycled through to first found cell
    If FoundCell.Address = FirstFound Then Exit Do
Loop
        
'Highlight cells that contain searchfor value
rng.Interior.ColorIndex = 34

Exit Sub

'Error Handler
NothingFound:
    MsgBox "No values were found in this worksheet"

End Sub

注：FindNext方法会自动继承首次Find方法的匹配规则，无需重复指定参数

内容的提问来源于stack exchange，提问作者Filip Warzocha