Search code examples
excelvbaunicodenumbersnon-latin

Highlight characters in cell that are not Latin or numbers

I have a column of codes that should only have numbers or Latin chars, but some of them may have been input incorrectly, that is some of them maybe Cyrillic or other. For example: РE09000047 or PE09000047.

I've got this code to work but it sees numbers as non-Latin:

Sub NonLatin()
Dim cell As Object
    For Each cell In Selection
        s = cell.Value
            For i = 1 To Len(s)
                If Mid(s, i, 1) Like "[0x0000-0x007F]" Then
                    cell.Interior.ColorIndex = 6
                    cell.Characters(i, l).Font.Color = vbRed
                    cell.Characters(i, l).Font.Bold = True
                End If
            Next
    Next
End Sub

It makes cell yellow, and non-Latin chars red and bolded. But also numbers. How can I make this work?

Solution

  • Highlight Non-Alpha-Numerics

    enter image description here

    Sub HighlightNonAlphaNumerics()
    
    ' Define constants.
    
    Const PROC_TITLE As String = "Highlight Non-Alpha-Numerics"
    Const CHAR_PATTERN As String = "[!A-Za-z0-9]" ' not alpha-numeric
    Const CELL_COLOR As Long = vbYellow
    Const CHAR_COLOR As Long = vbRed
    Const MSG_YES As String = _
        "Cells containing non-alpha-numeric characters highlighted"
    Const MSG_NO As String = _
        "No cells containing non-alpha-numeric characters found"
     
    ' Validate the selection.
    
    ' Check if there is no selection.
    If Selection Is Nothing Then
        MsgBox "There is no selection (""Nothing"")!", _
            vbExclamation, PROC_TITLE
        Exit Sub
    End If
    
    ' Check if the selection is no range.
    If Not TypeOf Selection Is Range Then
        MsgBox "The selection (""" & TypeName(Selection) _
            & """) is not a range!", vbExclamation, PROC_TITLE
        Exit Sub
    End If
        
    ' Clear specific selection formats.
    
    Application.ScreenUpdating = False
    
    With Selection
        .Interior.ColorIndex = xlNone
        .Font.ColorIndex = xlAutomatic
        .Font.Bold = False
    End With
    
    ' Loop through the cells and format the matching characters
    ' and combine each cell that was formatted into a unioned range
    ' to be highlighted later.
    
    ' Declare additional variables.
    Dim crg As Range, cell As Range, Value As Variant, i As Long
    Dim S As String, Char As String
    Dim IsCellFound As Boolean, IsValueValid As Boolean, IsCharFound As Boolean
    
    ' Loop.
    For Each cell In Selection
        Value = cell.Value
        ' Exclude errors and blanks.
        If Not IsError(Value) Then
            If Len(Value) > 0 Then
                IsValueValid = True
            End If
        End If
        ' Format characters.
        If IsValueValid Then
            S = CStr(Value)
            For i = 1 To Len(S)
                Char = Mid(S, i, 1)
                If Char Like CHAR_PATTERN Then
                    cell.Characters(i, 1).Font.Color = CHAR_COLOR
                    cell.Characters(i, 1).Font.Bold = True
                    IsCharFound = True
                End If
            Next i
            IsValueValid = False ' reset
        End If
        ' Combine cells.
        If IsCharFound Then
            If IsCellFound Then
                Set crg = Union(crg, cell)
            Else
                Set crg = cell
                IsCellFound = True ' never reset
            End If
            IsCharFound = False ' reset
        End If
    Next cell
    
    ' Highlight the cells.
    
    If IsCellFound Then crg.Interior.Color = CELL_COLOR
    
    Application.ScreenUpdating = True
    
    ' Inform.
    
    MsgBox IIf(IsCellFound, MSG_YES, MSG_NO) & " in """ _
        & "'" & Selection.Worksheet.Name & "'!" & Selection.Address(0, 0) _
        & """" & IIf(IsCellFound, ".", "!"), _
        IIf(IsCellFound, vbInformation, vbExclamation), PROC_TITLE
    
End Sub