VBA macro to count & highlight non-unicode cells

[Dimitris254]

here's my problem:

i work with an excel file that has cells primarily in English, however some of them are in Chinese or other non-Unicode (or whatever is the default Excel encoding) characters.

I would like a macro to change the font color of the cells with such characters, and maybe in the end also mentioning the total number of those cells.

 

[Worf]

Can you post the final solution here? Only excel forum members can view what is posted inside code tags there.
I wonder what was used: regular expressions, the like command, the instr function or something else…

 

[Dimitris254]

for sure!

code provided by jindon:

Sub highlight_nonascii()<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">
    Dim r As Range
    Dim n As Long
    
    Application.ScreenUpdating = False

    Cells.Interior.ColorIndex = xlNone
    With CreateObject("VBScript.RegExp")
        .Pattern = "[^\x20-\x7F]|\n"
        Cells.Replace vbLf, " ", 2
        For Each r In Range("a1", Cells.SpecialCells(11))
            If .test(r.Value) Then r.Interior.Color = vbRed: n = n + 1
        Next r
    End With
    MsgBox n & " cells were highlighted."
    
    Application.ScreenUpdating = True
    
End Sub

</code>
Cells.Replace vbLf, " ", 2 is to remove carriage return (new lines) characters before highlighting

 

[Rick Rothstein]

Cells.Replace vbLf, " ", 2 is to remove carriage return (new lines) characters before highlighting

Given that physically changes the values in the cells, why?

 

[Dimitris254]

Because newlines can cause problems in the output CSV afaik and i am aware of where the newlines are and i'd rather not have them highlighted - because i will replace them in end anyway.

The cells affected are usually address or office room, e.g.:

(original record in 1 cell)
Room 1,
Location A,
Country A

(after the macro)
Room 1, Location A, Country A