Highlight any cell without specific characters (lower case a-z) inside

excel, excel-formula, vba

Solution

The following worked for me:

Option Explicit
Sub NonAscii()
    Dim UsedCells   As Range, _
        TestCell    As Range, _
        Position    As Long, _
        StrLen      As Long, _
        CharCode    As Long

    Set UsedCells = ActiveSheet.Range("A1:A4271").CurrentRegion
    For Each TestCell In UsedCells
        StrLen = Len(TestCell.Value)
        For Position = 1 To StrLen
            CharCode = Asc(Mid(TestCell, Position, 1))
            If CharCode < 97 Or CharCode > 122 Then
                TestCell.Interior.ColorIndex = 36
                Exit For
            End If
        Next Position
    Next TestCell
End Sub

Problem

I have some garbled characters (or, not garbled, but non-English characters such as A's with Scandinavian accents, etc), and I need to ferret them out of around 80,000 entries. Can I write a formula to pick up and flag any cell that contains anything other than abcdefghijklmnopqrstuvwxyz?

Original source