excel - 查找特定文本、选择和突出显示

Question

我想找到并突出显示具有特定值的单元格。

在此示例中，我正在搜索数字 2。

该代码查找并突出显示编号为 2 的单元格，但它也突出显示编号为 22 和 23 的单元格，因为它们包含数字 2。

'Find Search Values on Sheet and Highlight

Sub Find_And_Highlight()

Dim Searchfor As String
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"
        
Set FoundCell = myRange.Find(what:=Searchfor, after:=Lastcell)
        
'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

标签： excelvba

Answer 1

请查看您需要实施的@Craig 提供的评论。即您需要修改如下Foundcell行：

Set FoundCell = myRange.Find(what:=Searchfor, after:=Lastcell, lookat:=xlWhole)

注意：此选项会修改用户在 Excel 中的搜索设置，因此以后请确保取消选中“查找”框中的以下选项。

---

---

但是，由于您正在更改单元格的背景颜色，因此您实际上不需要 VBA 来实现此目的。您可以使用条件格式 | 高亮单元格规则 | 等于如下图：

然后根据需要填写值：

结果将如下所示：