如何在VBA中查找并高亮不在指定列表中的字符

Got it, let's adjust your code so it highlights only the specific invalid characters instead of the entire cell. The key issue with your original code is that it applies the yellow fill to the whole cell as soon as it finds one invalid character, then exits the loop. We need to check every character individually and target just the bad ones.

Here's the revised code:

Sub ReplChars()
    Dim sCharOK As String, s As String
    Dim r As Range, rc As Range
    Dim j As Long
    
    sCharOK = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789,-.:;{}[]_"
    Set r = Worksheets("Sheet1").UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
    
    ' Loop through all text constant cells
    For Each rc In r
        ' Reset cell background to remove previous highlights
        rc.Interior.Color = xlColorIndexNone
        s = rc.Value
        
        ' Check each character individually
        For j = 1 To Len(s)
            ' If character isn't in allowed list, highlight it
            If InStr(sCharOK, Mid(s, j, 1)) = 0 Then
                rc.Characters(j, 1).Interior.Color = vbYellow
            End If
        Next j
    Next rc
End Sub

Key Changes Explained: #

• Removed Exit For: Your original code stopped checking characters as soon as it found one invalid one. Now we loop through every character in the cell to catch all invalid entries.
• Reset Cell Background: rc.Interior.Color = xlColorIndexNone clears any existing yellow fill before processing the cell, so old highlights don't linger if you re-run the macro.
• Target Individual Characters: Instead of coloring the whole cell, we use rc.Characters(j, 1).Interior.Color = vbYellow to apply the yellow fill only to the single invalid character at position j.

If you'd prefer to highlight characters with a font color instead of a background fill, just swap the line to:

rc.Characters(j, 1).Font.Color = vbRed ' Or any color you want

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