Excel VBA代码修改：实现多值匹配并批量高亮整行

问题说明 #

需求为在Excel工作表中查询两个关键词（"İmal Edilen"和"SLDPRT"），为包含任意一个关键词的行添加黄色高亮。当前VBA代码仅能高亮单行后循环停止，无法遍历全表所有匹配行。

原代码如下：

Sub SearchAndHighlightIE()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range
    Dim firstAddress As String
    Dim searchTerm As String

    ' Kullanicidan arama terimi al
    'searchTerm = InputBox("Aranacak kelimeyi giriniz:", "Arama Terimi")

    ' Eger kullanici bir sey girmeden iptal ederse, makroyu sonlandir
    'If searchTerm = "" Then Exit Sub

    ' Aktif çalisma sayfasini ayarla
    Set ws = ActiveSheet
  
    ' Arama yapilacak alan (tüm sayfa varsayilan olarak)
    Set searchRange = ws.UsedRange
  
    ' Arama terimiyle ilk bulusu bul
    Set foundCell0 = searchRange.Find(What:="Ýmal Edilen", LookIn:=xlValues, LookAt:=xlPart)
    Set foundCell1 = searchRange.Find(What:="SLDPRT", LookIn:=xlValues, LookAt:=xlPart)

    ' Eger bir sonuç bulunursa
    If Not foundCell0 Is Nothing Then
        firstAddress0 = foundCell0.Address
        
    If Not foundCell1 Is Nothing Then
        firstAddress1 = foundCell1.Address
        Do
            
            ' Hücreyi sariyla isaretle
            foundCell0.EntireRow.Interior.Color = RGB(255, 255, 153)
            ' Sonraki bulusu ara
            Set foundCell0 = searchRange.FindNext(foundCell0)
        Loop While Not foundCell0 Is Nothing And foundCell0.Address <> firstAddress0
    
    Else
            
        MsgBox "Aranan terim bulunamadi."
    End If
    End If
End Sub

代码问题分析 #

• 变量未声明：firstAddress0、foundCell0、foundCell1、firstAddress1未提前声明，隐式声明易引发逻辑错误。
• 嵌套逻辑错误：第二个关键词的判断嵌套在第一个关键词的判断内，导致仅当两个关键词都存在第一个匹配项时才执行循环；若第二个关键词不存在，直接弹出提示并终止流程，忽略第一个关键词的后续匹配。
• 循环覆盖不全：循环仅处理第一个关键词的匹配，未处理第二个关键词的所有匹配行。

修改后的代码 #

Option Explicit ' 强制变量声明，避免隐式声明错误

Sub SearchAndHighlightIE()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell0 As Range, foundCell1 As Range
    Dim firstAddress0 As String, firstAddress1 As String
    
    ' 设置当前活动工作表
    Set ws = ActiveSheet
    ' 设置查询范围为已使用区域
    Set searchRange = ws.UsedRange
    
    ' 处理第一个关键词："İmal Edilen"
    Set foundCell0 = searchRange.Find(What:="İmal Edilen", LookIn:=xlValues, LookAt:=xlPart)
    If Not foundCell0 Is Nothing Then
        firstAddress0 = foundCell0.Address
        Do
            ' 高亮整行
            foundCell0.EntireRow.Interior.Color = RGB(255, 255, 153)
            ' 查找下一个匹配项
            Set foundCell0 = searchRange.FindNext(foundCell0)
        ' 循环直到回到第一个匹配项或无更多匹配
        Loop While Not foundCell0 Is Nothing And foundCell0.Address <> firstAddress0
    End If
    
    ' 处理第二个关键词："SLDPRT"
    Set foundCell1 = searchRange.Find(What:="SLDPRT", LookIn:=xlValues, LookAt:=xlPart)
    If Not foundCell1 Is Nothing Then
        firstAddress1 = foundCell1.Address
        Do
            ' 高亮整行
            foundCell1.EntireRow.Interior.Color = RGB(255, 255, 153)
            ' 查找下一个匹配项
            Set foundCell1 = searchRange.FindNext(foundCell1)
        ' 循环直到回到第一个匹配项或无更多匹配
        Loop While Not foundCell1 Is Nothing And foundCell1.Address <> firstAddress1
    End If
    
    ' 可选：如果两个关键词都无匹配，弹出提示
    If (foundCell0 Is Nothing) And (foundCell1 Is Nothing) Then
        MsgBox "Aranan terim bulunamadi."
    End If
End Sub

修改要点说明 #

• 添加Option Explicit强制变量声明，避免隐式声明导致的错误。
• 将两个关键词的查询逻辑拆分，独立执行循环，确保每个关键词的所有匹配行都被高亮。
• 调整提示逻辑，仅当两个关键词都无匹配时才弹出提示，避免中断单个关键词的处理流程。
• 修复原代码中关键词拼写错误（Ýmal Edilen改为İmal Edilen，确保匹配准确）。

内容的提问来源于stack exchange，提问作者Gürkan Durukal