Conditional Formatting, How to test for Length, and numbers and letters only?

[Joyner]

Hello, I can't figure this one out and can't find it posted anywhere.

I want to do conditional formatting on cells to test for cells that don't meet the criteria of six characters long, that can be a combinations of numbers and letters only. The cell can be only numbers, or only letters, or any combination of numbers and letters, but no special characters, etc.

How would I write the conditional formatting formula to test for that?

Thanks for any help.

 

[Peter_SSs]

I'm not sure there is a straight-forward way to do that. Here is one way I think will do what you want.

Put this User-Defined Function in a standard module and use the uDF in the sheet as shown (column could be hidden) and apply the Conditional Formatting shown.

Post back with your Excel version if you need more detailed instructions.

<font face=Courier New><br><SPAN style="color:#00007F">Function</SPAN> SpecChar(s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Static</SPAN> RegEx <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> RegEx <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> RegEx = CreateObject("VBScript.RegExp")<br>        <SPAN style="color:#00007F">With</SPAN> RegEx<br>            .Global = <SPAN style="color:#00007F">True</SPAN><br>            .Pattern = "[^A-Za-z0-9]"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    SpecChar = -(Len(s) > 0 And (Len(s) <> 6 <SPAN style="color:#00007F">Or</SPAN> RegEx.Test(s)))<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br></FONT>

Excel Workbook
	A	B	C	D
1	123456			0
2	abc$ef			1
3	1234667			1
4	A7b5ER			0
5	t			1
6				0
7				0
8	FGH&*G			1
Special Cond Format

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =D1	Abc

 

[doofusboy]

Not sure how you would do that with Excel's conditional formatting, but the following should get you started on doing it via macro (VBA) [assumes you data is in column A, rows 1 through 5 - you can change to me your needs].

Dim i As Integer
Dim x As Integer
Dim boolCheck As Boolean
    For i = 1 To 5
        ' check to make sure 6 characters
        boolCheck = False
        If Len(Range("A" & i).Value) = 6 Then
            For x = 1 To 6
                ' check to make sure all characters are numbers or letters
                If (Asc(Mid(Range("A" & i).Value, x, 1)) >= 48 And _
                   Asc(Mid(Range("A" & i).Value, x, 1)) <= 57) Or _
                   (Asc(Mid(Range("A" & i).Value, x, 1)) >= 65 And _
                   Asc(Mid(Range("A" & i).Value, x, 1)) <= 90) Or _
                   (Asc(Mid(Range("A" & i).Value, x, 1)) >= 97 And _
                   Asc(Mid(Range("A" & i).Value, x, 1)) <= 122) Then
                   ' character is a letter or number
                   boolCheck = True
                Else
                    ' character is NOT a letter or number
                    boolCheck = False
                    Exit For
                End If
            Next x
            If boolCheck = True Then
                'add code to apply your formatting here for strings 6 characters long all letters and numbers
            Else
                'add code to apply alternate formatting here for strings NOT 6 characters long all letters and numbers
            End If
        Else
            'do nothing, go to next cell
        End If
    Next i

 

[Andrew Poulsom]

Try the conditional foematting formula:

=OR(LEN(A1)<>6,SUMPRODUCT(--(ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("1:6")),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"))))<>6)

where A1 is the active cell when it's entered.

 

[Peter_SSs]

Try the conditional foematting formula:

=OR(LEN(A1)<>6,SUMPRODUCT(--(ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("1:6")),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"))))<>6)

where A1 is the active cell when it's entered.

Andrew

That may possibly need one more check since it also highlights blank cells.


Further to my earlier suggestion the helper column is not really required. The UDF can be applied directly in the Conditional Formatting:

Excel Workbook
	A
1	123456
2	abc$ef
3	1234667
4	A7b5ER
5	t
6
7
8	FGH&*G
Special Cond Format

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =SpecChar(A1)	Abc

 

[Joyner]

Thanks to all for the quick replies. I think Andrew's solution will work best for my situation. I think the VBA solutions will work, but may need to be rerun if the user changes the input.

Andrew thanks you for the solution. Works great

 

[Andrew Poulsom]

andrew

that may possibly need one more check since it also highlights blank cells.

ok:

=and(len(a1)>0,or(len(a1)<>6,sumproduct(--(isnumber(search(mid(a1,row(indirect("1:6")),1),"abcdefghijklmnopqrstuvwxyz01234567890"))))<>6))

 

[Joyner]

Thanks Peter, for the update, I may give that a try, and thanks for the headsup on blank cells.

Thanks again Andrew,

I did modify your ealier formula to account for blanks cells as:

=IF(ISBLANK(C2),"",OR(LEN(C2)<>6,SUMPRODUCT(--(ISNUMBER(SEARCH(MID(C2,ROW(INDIRECT("1:6")),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"))))<>6))

It appears to work. Is yours a more solid formula to account for the blank cells. etc?

 

[Joyner]

P.S That's a nice bit of work on the formula that I will be able to apply to other situations...

 

[Joyner]

Hello again, I may need to post this to a new thread, but another question about conditional formatting. This one may need to be done with code.

I want to use conditional formating just to test a cell to see if it is a date, if not, do the formatting. Is that possible with just a conditional formatting formula?

Thanks