Excel VBA宏需求：高亮输入框中所有关键词而非仅首个实例

你的代码核心问题是只通过一次InStr查找关键词的首个匹配位置，没有循环遍历单元格内的所有实例。下面是修改后的代码，能高亮每个关键词在单元格内的所有出现次数：

Sub HighlightInputBoxText()
    Dim ws As Worksheet
    Dim strSearch As String
    Dim UserRange As Range
    Dim arySearch As Variant
    Dim cell As Range
    Dim searchTerm As String
    Dim startPos As Long, matchLen As Long
    
    Set ws = Sheets("Narratives")
    Set UserRange = ws.Range("N2:N10")
    
    strSearch = InputBox("Please enter text to highlight as a comma delimited list e.g. Abc,Xyz:", "Highlight Non-compliant Text")
    If strSearch = "" Then Exit Sub
    arySearch = Split(strSearch, ",")
    
    For Each cell In UserRange
        ' 先清除之前的高亮格式（可选，避免重复运行时格式混乱）
        cell.Font.Color = vbBlack
        cell.Interior.Color = xlNone
        
        For Each searchTerm In arySearch
            searchTerm = Trim(searchTerm) ' 去除关键词前后的空格
            If searchTerm = "" Then GoTo NextTerm ' 跳过空的关键词
            
            matchLen = Len(searchTerm)
            startPos = 1
            
            ' 循环查找当前关键词的所有实例
            Do
                startPos = InStr(startPos, UCase(cell.Value), UCase(searchTerm))
                If startPos > 0 Then
                    ' 高亮匹配的字符
                    cell.Characters(startPos, matchLen).Font.Color = RGB(255, 0, 0)
                    ' 设置单元格背景色
                    cell.Interior.Color = RGB(255, 255, 204)
                    ' 更新起始位置，查找下一个实例
                    startPos = startPos + matchLen
                End If
            Loop While startPos > 0
NextTerm:
        Next searchTerm
    Next cell
End Sub

关键修改说明： #

• 新增Do...Loop While循环，持续查找当前关键词在单元格内的所有匹配项，直到InStr返回0（无匹配）
• 每次找到匹配后，将下次查找的起始位置设为startPos + matchLen，避免重复匹配同一位置
• 加入Trim(searchTerm)处理输入的关键词，避免用户输入带空格的无效项
• 可选的格式清除步骤，解决重复运行宏时格式叠加的问题

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