Highlight cell that doesn't have only letters-CodePudding

I'm currently trying to develop my first conditional formatting on VBA, but after hours of trial it still doesn't work.

I'm aiming for a formula that would change the background / highlight the text of a cell that contains something else than any alphabet letters (not sensitive to caps or not). Accents, numbers and special characters would be the trigger

Here is my current code Thank you in advance for your help

Sub Highlight()
    Dim MyRange As Range
    Set MyRange = Selection
    
MyRange.FormatConditions.Delete
MyRange.FormatConditions.Add xlExpression, , Formula1:="=IsAlpha()=false"
MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)

End Sub

And IsAlpha would be a function like

Function IsAlpha(s) As Boolean
    IsAlpha = Len(s) And Not s Like "*[!a-zA-Z ]*"
End Function

CodePudding user response：

You need to pass an argument to IsAlpha. Try the following:

Sub Highlight()
    Dim MyRange As Range
    Set MyRange = Selection
    
    MyRange.FormatConditions.Delete
    
    Dim s As String
    s = "=NOT(IsAlpha(" & MyRange.Cells(1).Address(False, False) & "))"
    
    MyRange.FormatConditions.Add xlExpression, Formula1:=s
    MyRange.FormatConditions(1).Interior.Color = vbRed 'Or use RGB...
End Sub

In action:

CodePudding user response：

Highlight Not Pure Alpha

There is a delay of about 2s before the cells get colored (on my machine). I wonder if a worksheet change would handle this smoother (if the range contains values (not formulas)).

Option Explicit

Sub HighlightNotPureAlphaTEST()
    If TypeOf Selection Is Range Then
        HighlightNotPureAlpha Selection
    End If
End Sub

Sub HighlightNotPureAlpha(ByVal rg As Range)
    With rg
        .FormatConditions.Delete
        ' To not highlight blanks...
        .FormatConditions.Add xlExpression, , _
            "=NOT(IsAlphaOrBlank(" & .Cells(1).Address(0, 0) & "))"
        ' To highlight blanks:
        '.FormatConditions.Add xlExpression, , _
            "=NOT(IsAlpha(" & .Cells(1).Address(0, 0) & "))"
        .FormatConditions(1).Interior.Color = RGB(255, 0, 0)
    End With
End Sub

Function IsAlphaOrBlank(ByVal S As String) As Boolean
    Application.Volatile
    IsAlphaOrBlank = Not S Like "*[!A-Za-z]*"
End Function

Function IsAlpha(ByVal S As String) As Boolean
    Application.Volatile
    If Len(S) > 0 Then IsAlpha = Not S Like "*[!A-Za-z]*"
End Function