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

Thursday, February 25, 2016

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 


Elseif A1=20 then


Elseif A1=30 then





Excel formula code placed in cell B1:


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


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

Try this solution and you will get the same result.