Highlight cells with text in different languages

[Lux Aeterna]

Hello everyone!

I've got an excel file that we add names in both Greek and Latin characters. Some of these characters are common in both languages and, sometimes, words are carelessly written half in English half in Greek. This is a problem when we try to find a name in our file.

Is it possible to highlight cells, preferably using conditional formatting, that are written in two different languages?

Thank you in advance!

 

[Anthony47]

Does that mean that the cells contain 2 different fonts? Could you share at least an image with English and Greek content?

 

[Lux Aeterna]

Does that mean that the cells contain 2 different fonts? Could you share at least an image with English and Greek content?

Not sure if by two "different fonts" you refer to fonts like Arial and Calibri, or the languages. For example in the attached picture the first two letters are Greek and the rest are English. But you can't tell the difference, because they look the same. I was wondering if excel can recognise the language inconsistency in the cell.

 

[Anthony47]

That is an E it's neither English nor Italian nor Greek...
If that string is in B2, what the following formula will return?

=CODE(B2)

In my naive thinking I had in mind a situation like the one in the image

 

[Lux Aeterna]

That is an E it's neither English nor Italian nor Greek...
If that string is in B2, what the following formula will return?

=CODE(B2)

In my naive thinking I had in mind a situation like the one in the image

When I mix both languages, I get 197. When I type the entire cell in English I get a 69. I think the return of the formula depends on the content, not the language.
1st is mixed (first letter is Greek and the rest are English), 2nd is English, 3rd is the same letters as in the first but all Greek, 4th is mixed (first letter is English and the rest are Greek).

ΕΧCEL IS AMAZING	197
EXCEL IS AMAZING	69
ΕΧΨΕΛ ΙΣ ΑΜΑΖΙΝΓ	197

ΕΧΨΕΛ ΙΣ ΑΜΑΖΙΝΓ

197

 

[Lux Aeterna]

When I mix both languages, I get 197. When I type the entire cell in English I get a 69. I think the return of the formula depends on the content, not the language.
1st is mixed (first letter is Greek and the rest are English), 2nd is English, 3rd is the same letters as in the first but all Greek, 4th is mixed (first letter is English and the rest are Greek).

ΕΧCEL IS AMAZING	197
EXCEL IS AMAZING	69
ΕΧΨΕΛ ΙΣ ΑΜΑΖΙΝΓ	197

ΕΧΨΕΛ ΙΣ ΑΜΑΖΙΝΓ

197

4th row gives me a 69. I mistyped it.

 

[Anthony47]

Try the following macro and see if it gives useful information:

Sub Grenglish()
Dim myC As Range, Gr As Boolean, En As Boolean
Dim myCV
'
For Each myC In Selection
    myCV = myC.Value
    Gr = False: En = False
    For I = 1 To Len(myC.Value)
        If Asc(Mid(myCV, I, 1)) > 150 Then Gr = True
        If Asc(Mid(myCV, I, 1)) < 130 Then En = True
    Next I
    If Gr And En Then
        myC.Interior.Color = RGB(255, 100, 100)
    ElseIf Gr Then
        myC.Interior.Color = RGB(100, 255, 100)
    ElseIf En Then
        myC.Interior.Color = RGB(150, 150, 255)
    End If
Next myC
End Sub

Select a Range of cells, then start the macro; it will assign a reddish to guessed mixed language, bluish to guessed English only, greenish to guessed Greek only

But I am on an unknown area (unknown to me: how Windows deals with the languages)

 

[Anthony47]

No, maybe this :

Sub Grenglish()
Dim myC As Range, Gr As Boolean, En As Boolean
Dim myCV
'
For Each myC In Selection
    myCV = myC.Value
        Gr = False: En = False
        For I = 1 To Len(myC.Value)
            If AscW(Mid(myCV, I, 1)) <> 32 Then
                If AscW(Mid(myCV, I, 1)) > 250 Then Gr = True
                If AscW(Mid(myCV, I, 1)) < 130 Then En = True
            End If
        Next I
        If Gr And En Then
            myC.Interior.Color = RGB(255, 100, 100)
        ElseIf Gr Then
            myC.Interior.Color = RGB(100, 255, 100)
        ElseIf En Then
            myC.Interior.Color = RGB(150, 150, 255)
        End If
Next myC
End Sub

 

[Lux Aeterna]

No, maybe this :

Sub Grenglish()
Dim myC As Range, Gr As Boolean, En As Boolean
Dim myCV
'
For Each myC In Selection
    myCV = myC.Value
        Gr = False: En = False
        For I = 1 To Len(myC.Value)
            If AscW(Mid(myCV, I, 1)) <> 32 Then
                If AscW(Mid(myCV, I, 1)) > 250 Then Gr = True
                If AscW(Mid(myCV, I, 1)) < 130 Then En = True
            End If
        Next I
        If Gr And En Then
            myC.Interior.Color = RGB(255, 100, 100)
        ElseIf Gr Then
            myC.Interior.Color = RGB(100, 255, 100)
        ElseIf En Then
            myC.Interior.Color = RGB(150, 150, 255)
        End If
Next myC
End Sub

It's column C that I want to check for mixed languages. Could you please edit your macro, because I don't know how to do it? I'll try that on Monday that I'll get back to office. If you come up with any conditional formatting idea, I'd rather use that one. Thanks again!

 

[Anthony47]

The macro is a prototype; if the results are reasonable we can work on it, otherwise we have to look to a different approach
So first check it using the "Selected Range" and tell us which is the result