Results 1 to 3 of 3

Coding for if a cell contains the proper information

Threaded View

  1. #1
    Registered User
    Join Date
    05-08-2018
    Location
    Hazelton, Canada
    MS-Off Ver
    2013
    Posts
    19

    Coding for if a cell contains the proper information

    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
    Attached Files Attached Files
    Last edited by ReganK; 08-22-2018 at 06:01 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Userform not adding data to correct place
    By Ms2BSwagg in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-13-2018, 05:15 AM
  2. Replies: 10
    Last Post: 07-14-2015, 12:26 AM
  3. Replies: 0
    Last Post: 11-21-2014, 05:00 PM
  4. [SOLVED] ensure user selects radio button before adding information
    By JamesT1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-27-2014, 10:00 AM
  5. [SOLVED] Check cells are correct format and contain correct data
    By rikosborne in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-26-2014, 02:53 PM
  6. [SOLVED] How to ensure the coming data is correct?
    By freeSky in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-21-2012, 05:45 AM
  7. [SOLVED] Formula which checks whether a string is found in a range and checks 2 criteria
    By liranbo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-30-2012, 05:28 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1