Wednesday, 19 November 2014

An Excel Macro to find all non-ascii characters in a sheet and highlight them

An Excel Macro to find all non-ascii characters in a sheet and highlight them


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

    UsedCells = ActiveSheet.Range("A1").CurrentRegion
    For Each TestCell In UsedCells
        StrLen = Len(TestCell.Value)
        For Position = 1 To StrLen
            CharCode = Asc(Mid(TestCell, Position, 1))
            If CharCode < 32 _
                Or (CharCode > 32 And CharCode < 48) _
                Or (CharCode > 57 And CharCode < 65) _
                Or (CharCode > 90 And CharCode < 97) _
                Or CharCode > 122 Then
                  TestCell.Interior.ColorIndex = 36
                  Exit For
            End If
        Next Position
    Next TestCell
End Sub