如何用VBA实现多文本字符串部分匹配的条件格式设置

方案1：直接批量设置单元格底色（高效遍历版） #

这个方案一次性读取所有关键词，仅遍历H列有数据的区域，大幅提升运行效率，同时支持A1、B1、C1等第一行所有非空关键词的部分匹配：

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim keywordsRange As Range
    Dim keywords() As String
    Dim keyword As Variant
    Dim myrange As Range
    Dim cell As Range
    Dim cellValue As String
    Dim i As Integer
    
    ' 指定目标工作表，避免激活切换
    Set ws = ThisWorkbook.Worksheets("Workbook")
    
    ' 获取第一行从A1开始的所有非空关键词单元格
    Set keywordsRange = ws.Range("A1", ws.Cells(1, ws.Columns.Count).End(xlToLeft))
    ' 将关键词转成小写数组，减少重复转换操作
    keywords = Application.Transpose(Application.Transpose(keywordsRange.Value))
    For i = LBound(keywords) To UBound(keywords)
        keywords(i) = LCase(keywords(i))
    Next i
    
    ' 获取H列有数据的区域（避免遍历整列浪费资源）
    Set myrange = ws.Range("H1", ws.Cells(ws.Rows.Count, "H").End(xlUp))
    ' 清除H列原有底色
    myrange.Interior.Pattern = xlNone
    
    ' 遍历单元格，检查是否匹配任意关键词
    For Each cell In myrange
        If Not IsEmpty(cell.Value) Then
            cellValue = LCase(cell.Value)
            ' 匹配到任意关键词后立即跳出循环，减少不必要的比对
            For Each keyword In keywords
                If InStr(cellValue, keyword) > 0 Then
                    cell.Interior.ColorIndex = 4
                    Exit For
                End If
            Next keyword
        End If
    Next cell
End Sub

关键优化点： #

• 批量读取关键词：一次性获取第一行所有非空关键词并转成数组，减少单元格读取次数
• 缩小遍历范围：仅处理H列有数据的区域，而非整列，大幅降低循环次数
• 统一转小写：提前将关键词和单元格值转小写，避免重复转换操作
• 提前终止匹配：单元格匹配到任意一个关键词就跳出循环，减少无效比对

方案2：用VBA创建条件格式规则（自动更新） #

如果希望后续H列新增数据或修改关键词时自动高亮匹配项，可以用VBA创建条件格式规则，无需重复运行代码：

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim keywordsRange As Range
    Dim ruleFormula As String
    
    Set ws = ThisWorkbook.Worksheets("Workbook")
    ' 获取第一行非空关键词区域
    Set keywordsRange = ws.Range("A1", ws.Cells(1, ws.Columns.Count).End(xlToLeft))
    
    ' 构建条件格式公式：检查H列单元格是否包含任意关键词
    ruleFormula = "=SUMPRODUCT(--ISNUMBER(SEARCH(" & keywordsRange.Address(True, True, xlA1, True) & ",H1)))>0"
    
    ' 清除H列原有条件格式
    ws.Range("H:H").FormatConditions.Delete
    ' 添加新的条件格式规则
    With ws.Range("H:H").FormatConditions.Add(Type:=xlExpression, Formula1:=ruleFormula)
        .Interior.ColorIndex = 4
    End With
End Sub

说明： #

• 公式SUMPRODUCT(--ISNUMBER(SEARCH(关键词区域,H1)))>0会自动检查H列单元格是否包含关键词区域中的任意文本，SEARCH支持部分匹配且不区分大小写
• 后续修改第一行的关键词或在H列新增数据时，条件格式会自动更新高亮，无需重新运行VBA

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