Conditional Formating on Non-Letters and Non-Numbers

[Jet35]

First time poster, long time reader, thanks for all the past help and on this question...
I have a doc that several other employees use and one field requires users to input a Unique Name. The Unique Name can only use Letters and Numbers though, so I'd like to do conditional formatting when a user enters anything that is NOT a Letter or Number. Any tips?

 

[lrobbo314]

How about this?

Right click on the sheet you are working with. Click view code. Paste the following

Private Sub Worksheet_Change(ByVal Target As Range)
Dim t As Integer
For i = 1 To Len(Target)
    t = Asc(Mid(Target, i, 1))
    If Not ((t >= 48 And t <= 57) Or (t >= 65 And t <= 90) Or (t >= 97 And t <= 122)) Then
        With Target.Characters(i, 1)
            .Font.Bold = True
            .Font.Color = vbRed
        End With
    End If
Next i
End Sub

 

[Eric W]

You can use this as a Conditional Formatting formula:

=SUMPRODUCT(--ISERROR(SEARCH("~"&MID(J1&"aaaaaaaaaa",ROW(INDIRECT("1:10")),1),"abcdefghijklmnopqrstuvwxyz0123456789")))

It does assume the Unique Name is 10 characters or less.

 

[lrobbo314]

Eric W, that's a cool formula. Can you explain the purpose of the "~" in the Search function? Why is that have to be in there?

 

[Eric W]

Thanks for the kind words about my formula! I originally tried to create one using the same type of logic you used in your code, using CODE, but it got ugly real fast.

The SEARCH function allows wildcards, specifically "*" and "?". So if someone uses a * or ?, then the formula wouldn't catch it since they'd match anything. If you want to make SEARCH look for an actual * or ?, then you have to precede it with a ~. And fortunately, it doesn't hurt to put a tilde in front of any other letter.

One thought about your code: you may want to clear the formatting if they enter a valid user id, otherwise if they enter a bad one, then fix it, it won't show as fixed.

 

[lrobbo314]

Cool, great to know. I really like these formulas and this is a great solution.

Having said that, if you want to go the VBA route, I adjusted the code to account for some error handling.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count = 1 Then
        Dim t As Integer
        For i = 1 To Len(Target)
            t = Asc(Mid(Target, i, 1))
            If Not ((t >= 48 And t <= 57) Or (t >= 65 And t <= 90) Or (t >= 97 And t <= 122)) Then
                With Target.Characters(i, 1)
                    .Font.Bold = True
                    .Font.Color = vbRed
                End With
            End If
        Next i
    End If
End Sub

 

[Jet35]

You can use this as a Conditional Formatting formula:

=SUMPRODUCT(--ISERROR(SEARCH("~"&MID(J1&"aaaaaaaaaa",ROW(INDIRECT("1:10")),1),"abcdefghijklmnopqrstuvwxyz0123456789")))

It does assume the Unique Name is 10 characters or less.

Thanks Eric. The Unique Name can be up to 27 characters, but this doesn't seem to be working for me. Here's some more detail:
1) Highlighted the Unique Name column (I9:J29, it's a merged cell for ascetic reasons b/c of cells above them to make it easier for other people to read, but I can split if need be)
2) Conditional Formatting > New Rule > Use formula to determine which cells to format
3) Copy/pasted your formula, didn't work
4) Tried making adjustments (increased the numbers of "aaaa" to 27, adjusted the J1 reference to $I9, I9:J29, but nothing is working)

I appreciate your help. Let me know if there's any other info I could provide that would be helpful.

 

[Eric W]

I experimented a bit with merged cells, and I got this to work:

1) Select I9:J29
2) CF > New Rule > Use Formula
3) =SUMPRODUCT(--ISERROR(SEARCH("~"&MID(I9&REPT("a",27),ROW(INDIRECT("1:27")),1),"abcdefghijklmnopqrstuvwxyz0123456789")))

CF is a bit finicky, you have to get it just right. The I9 in the formula, without $s, must be the upper left cell in your range. If a space is allowed in the user name, add it to the allowed string.

Let me know if this works better!

 

[Jet35]

I experimented a bit with merged cells, and I got this to work:

1) Select I9:J29
2) CF > New Rule > Use Formula
3) =SUMPRODUCT(--ISERROR(SEARCH("~"&MID(I9&REPT("a",27),ROW(INDIRECT("1:27")),1),"abcdefghijklmnopqrstuvwxyz0123456789")))

CF is a bit finicky, you have to get it just right. The I9 in the formula, without $s, must be the upper left cell in your range. If a space is allowed in the user name, add it to the allowed string.

Let me know if this works better!

This works great! Thanks so much Eric I have no idea how it works, but I look forward to playing with it to figure it out.

 

[Eric W]

I'm glad it works for you!

If you paste the formula in a regular cell, you can use the Evaluate Formula button on the Formulas tab to walk through it. I use that a lot!