Excel: Data validation 3 uppercase + 3 numbers

Hello, I need to prohibit anything typed into the range O5:O500, except for this format (vehicle registration numbers): AAA111 Within the range there can't be any duplicates either, so in my example, if AAA111 is in cell O5, then the same AAA111 can't be used in O6:O500. I've created a macro which changes lowercase letters to upper, which works, but I need to add the other functions as well. The code below doesn't need to be present, I reckon more efficient integrated codes works better.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("O5:O500")) Is Nothing Then
With Target
If UCase(.Text) <> .Text Then
    .Value = UCase(.Text)
End If
End With
End If
End Sub
Any suggestions? Greatful for help on this!

This question generated 29 answers. To proceed to the answers, click here.

This thread is current as of March 27, 2014.

For more resources for Microsoft Excel