Sunday, February 28, 2016

Excel VBA: Detect arabic letters in cell

Tags: Excel VBA - Detect Arabic letters in cell - Detect non-English letters in cell

My case is: I have an Excel sheet which is updated daily. Each cell can contain Arabic letters only, English letters only, or both Arabic and English letters. Since I like specific font for Arabic "Sakkal Majalla" and specific font for English "Tahoma"  I used to edit fonts manually for each cell I edit (which was irritating and time-consuming) till I figured out how to simply automate this process with a simple tweak.

The following is a preview for "Sakkal Majalla" font:

The following is a preview for "Tahoma" font:

The next is VBA code which is simple (may be not efficient, but for me it worked like magic):

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

For j = 1 To Target.Cells.Count   ' if multiple cells are edited once (by pasting cells from other place)

If IsNumeric(Target.Cells(j).Value) = True And Target.Cells(j).Value <> "" Then    'if cell contains numbers only then it will contain zero characters

Target.Cells(j).Font.Name = "Tahoma"

Target.Cells(j).Font.Size = 9

Else ' cell contains text/sring

OldFontSize = Target.Cells(j).Font.Size

For i = 1 To Target.Cells(j).Characters.Count

If InStr(1, "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789", Target.Cells(j).Characters(Start:=i, Length:=1).Text) < 1 Then

With Target.Cells(j).Characters(Start:=i, Length:=1).Font
        .Name = "Sakkal Majalla"
        .Size = OldFontSize
End With


With Target.Cells(j).Characters(Start:=i, Length:=1).Font
        .Name = "Tahoma"
        .Size = 9
End With

End If

Next i

End If

Next j

Application.ScreenUpdating = True

End Sub