Using conditional formatting to identify if a cell contains any symbols

hvl888

New Member
Joined
May 6, 2018
Messages
6
Hi,

Hoping someone can help.

Does anyone know if I can use conditional formatting to flag a cell that contains any symbols that are not alpha or numeric. For example

if cell A1 = ABC,123 I want it to fill the cell in red

Any help would be greatly appreciated.

Many Thanks
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
This is probably slightly slower than using a direct formula, but I think it will be far more readable when reviewing your setup 6 months from now. Put this UDF (user defined function) in a general code module...
Code:
Function IsAlphaNumeric(Txt As String) As Boolean
  IsAlphaNumeric = Txt Like "*[!A-Za-z0-9 ]*"
End Function
and then assuming the active cell in your selected range is A1, use this formula in your Conditional Formatting rule...

=IsAlphaNumeric(A1)
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,189
Love your work, Rick

for understanding in 6 months, maybe better named something like IsNotAlphaNumeric :)

regards, Fazza
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
Love your work, Rick

for understanding in 6 months, maybe better named something like IsNotAlphaNumeric :)
Ugh, I forgot the Not keyword. :banghead:

I meant to write the function this way...
Code:
Function IsAlphaNumeric(Txt As String) As Boolean
  IsAlphaNumeric = [B][COLOR="#FF0000"]Not[/COLOR][/B] Txt Like "*[!A-Za-z0-9 ]*"
End Function
Thanks for noticing that I had done something wrong.

I included a space character as an alpha character, but in thinking about it, the OP may not have wanted that. If that is the case, the OP should remove the space before the closing square bracket.
 
Last edited:

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,189
I did notice that space.

I'm getting knotted in the nots, Rick. If putting the Not in the function, then should the conditional formatting rule be = NOT(IsAlphaNumeric(A1))

Hence my earlier comment to rename the function to IsNotAlphaNumeric & use it in the conditional formatting.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
I did notice that space.

I'm getting knotted in the nots, Rick. If putting the Not in the function, then should the conditional formatting rule be = NOT(IsAlphaNumeric(A1))

Hence my earlier comment to rename the function to IsNotAlphaNumeric & use it in the conditional formatting.
You are correct on all counts. In thinking about it, I think your original suggestion makes the most sense given what the OP asked. The OP must be thoroughly confused by now. To help straighten him out, here is the function that I should have posted originally...
Code:
Function v(Txt As String) As Boolean
  IsAlphaNumeric = Txt Like "*[!A-Za-z0-9 ]*"
End Function
and then use this Conditional Formatting formula...

=IsNotAlphaNumeric(A1)

I think I have it all straight now, so a large thank you to Fazza for the above.
 
Last edited:

Forum statistics

Threads
1,081,677
Messages
5,360,451
Members
400,586
Latest member
Minty

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top