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 Else 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

Excel nested If trick

Tags: Excel nested If trick, Nesting more than 7 IF statements in an Excel function, Alternatives To Nested IF Functions, How can I nest more than 7 IF statements together?, Simplified nested If.

Assume the following simplified case: we want cell B1 to have certain value based of the value of cell A1.

The psuedo code is:

If A1=10 then 

B1=A1*2

Elseif A1=20 then

B1=A1^2

Elseif A1=30 then

B1=3*A1^3

Else

B1=0

EndIf

Excel formula code placed in cell B1:

=IF(A1=10,A1*2,IF(A1=20,A1^2,IF(A1=30,3*A1^3,0)))

The previous formula called "Nested If" which means If statement inside another If statement.

For sure this Excel formula seems to be complex and it will get complicated by increasing the number of cases. In addition, Excel can't accept more than 7 nested If statments.

The tricky solution (without using VBA) is to use separate If statements summed together instead of nested If.  See the formula below

=IF(A1=10,A1*2,0)+IF(A1=20,A1^2,0)+IF(A1=30,3*A1^3,0)

The above solution is easy to read, revise, and edit later.

Try this solution and you will get the same result.