need vba to verify 10 numbers only

[gibsongk55]

Hi,

How could I use VBA in excel to verify a phone number. Only number 10 digits. If not 10 numbers or has hyphens or other characters, then alert me.

Thanks,

Gibs

 

[gibsongk55]

Hi,

Thanks for the help. Trebor that worked fine. Mike that code still would not work. Tried them both.


Thanks again,

Gibs

 

[Peter_SSs]

Here's another slight variation. If 'bad' cells exist, the code displays just those rows and hides the rest. Not sure if that is any use to you.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Bad_Numbers()<br>    <SPAN style="color:#00007F">Dim</SPAN> rData <SPAN style="color:#00007F">As</SPAN> Range, rBad <SPAN style="color:#00007F">As</SPAN> Range, rCel <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rData = Range("T1", Range("T" & Rows.Count).End(xlUp))<br>    <SPAN style="color:#00007F">Set</SPAN> rBad = rData.Offset(, 1)<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rCel <SPAN style="color:#00007F">In</SPAN> rData<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> (rCel.Value = vbNullString <SPAN style="color:#00007F">Or</SPAN> rCel.Text <SPAN style="color:#00007F">Like</SPAN> "##########") <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> rBad = Union(rBad, rCel)<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> rCel<br>    <SPAN style="color:#00007F">Set</SPAN> rBad = Intersect(rBad, rData)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> rBad <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        rData.EntireRow.Hidden = <SPAN style="color:#00007F">True</SPAN><br>        rBad.EntireRow.Hidden = <SPAN style="color:#00007F">False</SPAN><br>        ActiveWindow.ScrollRow = rBad.Row<br>        Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>