My job uses a lot of C-cans which are labeled as 4 letters followed by 7 numbers, and contain different material inside them that is also numbered.
On the packing slips I generate for this cargo it is very important to not have any mistakes - so I had help making a macro to turn cells RED if they didn't contain the correct info ie."4 letters followed by 7 numbers". (See below for the Can# check)
Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Cells(7, 7)) Is Nothing Then 'Change Range to suit
With CreateObject("vbscript.regexp")
.Pattern = "\b[A-Za-z]{4}[0-9]{7}\b"
.Global = True
If Not .test(Target) Then
Target.Interior.Color = vbRed
Else
Target.Interior.Color = xlNone
End If
End With
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
I'd like to do something similar for multiple other boxes. Some things to add are:
1) Cell C5 must contain the text "Shipment #" followed by 2 numbers [0-9]{2} example: Shipment # 02
2) Cell C7 must contain the text "Seal # UL-" followed by 7 numbers [0-9]{7} example: Seal # UL-1234567
3) Cell G5 must contain the date in format of 11.11.11
4) Cell G6 must contain 8 numbers followed by a "-" then 2 numbers example: 12341234-12
5) Then cells B18->B26, F18->26, G18->26, and G38 must contain at least 1 number.
I've tried tackling this on my own - for instance for #1, with stuff like:
If Not Intersect(Target, Cells(3, 5)) Is Nothing Then 'Change Range to suit
With CreateObject("vbscript.regexp")
.Pattern = "\bShipment #[0-9]{2}\b"
.Global = True
If Not .test(Target) Then
Target.Interior.Color = vbRed
Else
Target.Interior.Color = xlNone
End If
End With
End If
but I'm not so familiar with how these things work, or where I should be copy and pasting that code to. Ie. within the sub of the last macro? on a new one with the option explicit text again? etc.
(Attached a blank copy of the form I'm using)
Any help would be greatly appreciated!! Thanks in advance
Bookmarks