Highlight everything except English letters A-Z

[Agarwal2ss]

Hello ,
I want either Excel Formula or VBA code so that it can highlight everything in a column (Numbers, special characters, spaces, all foreign alphabets except English letters.) with some color(Red) and cell should be highlighted with Yellow color.

Eg. Cell A1 contains word like "World@1234" than it should highlight special characters(including numbers) and cell should be highlighted with Yellow color.

Result= World@1234(with yellow cell)

Regards
SA

 

[Rick Rothstein]

For text (not formulas) in Column A, give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub ColorNonLettersOnly()
  Dim X As Long, Cell As Range
  Application.ScreenUpdating = False
  For Each Cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    If Len(Cell.Value) Then
      For X = 1 To Len(Cell.Value)
        If Mid(Cell.Value, X, 1) Like "[!A-Za-z]" Then Cell.Characters(X, 1).Font.Color = vbRed
      Next
      Cell.Interior.Color = vbYellow
    End If
  Next
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[JoeMo]

This can be applied to non-contiguous cells if needed. First select all the cells you want altered, then run the macro.

Sub HighlightExceptAtoZ()
'Select the cells you want to apply this to first, then run this macro
Dim R As Range, cel As Range, ct As Long
If TypeName(Selection) = "Range" Then
    Set R = Selection
Else
    MsgBox "Select a range of cells then try again"
    Exit Sub
End If
On Error Resume Next
Set R = R.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If R Is Nothing Then
    MsgBox "Can't apply this macro to formulaic cells"
    Exit Sub
End If
Application.ScreenUpdating = False
For Each cel In R
    For i = 1 To Len(cel.Value)
        If Mid(cel.Value, i, 1) Like "[!A-Za-z]" Then
            ct = ct + 1
            cel.Characters(i, 1).Font.Color = vbRed
        End If
    Next i
    If ct > 0 Then cel.Interior.Color = vbYellow
    ct = 0
Next cel
Application.ScreenUpdating = True
End Sub