Checking that a cell contains 4 numbers followed by 7 numbers

In my line of work we use sea-cans to transport a great deal of goods.
The sea-cans are always tracked by an 11 digit code which is 4 letters followed by 7 numbers.
Example: ABCD1234567

When I am entering these sea-cans into our database, sometimes a number is missed, or an extra one is entered, etc.
I'm looking to create some sort of highlight-cell function or IF function to return the cell red if it does not contain exactly 11 digits (4 letters followed by 7 numbers).

Any help would be greatly appreciated!

Regan

VBA solution

Please Login or Register  to view this content.

Right Click on tab where data is to be entered, "View Code" and copy/paste the above.

Assumes data is column A: change highlighted to required column.

file save with xlsm extension.

Awesome! Thanks so much
and if I want it to target a single cell? (the Excel in my manifest template where I enter the can information is G7)
Sorry still learning

Change:

Please Login or Register  to view this content.

Will this work:

Private Sub Worksheet_Change(ByVal Target As Range)

'Do nothing if more than one cell is changed or content deleted

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

'Stop any possible runtime errors and halting code

On Error Resume Next

Application.EnableEvents = False

If Target.Cell = G7 Then ' Check data entry is column G

If Len(Target) <> 11 Then GoTo error_value ' Wrong length

For i = 1 To 4
If Asc(UCase(Mid(Target, i, 1))) < 65 Or Asc(UCase(Mid(Target, i, 1))) > 90 Then GoTo error_value 'Not alpha
Next i

If Not IsNumeric(Right(Target, 7) + 0) Then GoTo error_value ' not numeric

Application.EnableEvents = True ' All OK
Exit Sub


error_value:

MsgBox Target.Value & " is an invalid entry"

Application.EnableEvents = True

On Error GoTo 0

End If




End Sub
Sub x()
Application.EnableEvents = True
End Sub

No

Please Login or Register  to view this content.

This ONLY checks G7.

Perfect - that's what I'm after. Thanks a ton!!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.