Damn!! That is pretty awesome!!


--
RyGuy


"Rick Rothstein (MVP - VB)" wrote:

Put the following event procedure into the code window for the worksheet you
want this functionality on (right click the worksheet tab, select View Code
from the popup menu, copy/paste the code into the code window that
appears)...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Answer As String
If Not Intersect(Target, Range("A:A,C:C,E:E")) Is Nothing Then
On Error GoTo Whoops
Application.EnableEvents = False
Answer = Val(Left(Target.Value, Len(Target.Value) - 1)) & _
"-" & UCase(Right(Target.Value, 1))
If Answer Like "####-[A-Z]" Or Answer Like "#####-[A-Z]" Then
Target.Value = Answer
Else
MsgBox "Your entry is not of the proper shape!", vbExclamation
Target.Select
End If
End If
Whoops:
Application.EnableEvents = True
End Sub

You do need to change the column references in the first If-Then statement
from the Range("A:A,C:C,E:E") I used to the 3 columns you want this check
performed on (at least, that is what I think you asked for... if you are
only inputting the Vehicle Number into one column, then replace the 3 column
references with just a single reference for the column you are interested
in).

Rick



"jnf40" wrote in message
...
I have a workbook with 3 columns that have information the user inputs.
Columns C and H have 20 rows from 9 to 28 and Column M has 15 rows from 9
to 23. What I need is when the user enters a Vehicle number, which will be
4
or 5 numbers followed by an alpha character, depending on how they enter
the
number I always want it to format the same way, and then check for
duplicates, for example: if they enter 3442g or 3442G or 3442-g or 3442-G
or
3442 -g or 3442 -G or 3442- g or 3442- G or 3442 - g or 3442 - G etc. it
would always end up showing as 3442-G. The numbers and alpha characters
will
always be different and there could be 5 numbers instead of 4 such as
36857-H. Any and all help is greatly appreciated.