VBA to group and identify

[jpitcairn]

Hello. I've been going through a database export of our part codes at work and I am a VBA novice. I'm trying to isolate wrong part numbers and the correct part numbers start with letters. In Column D, I want to be able to identify the wrong ones with "Invalid Part Number" in a yellow highlighted cell, then isolate the valid part numbers as "In-House", for our own proprietary parts, and "Outsource" for those that we purchase. Our in-house part numbers, in column C, start with even numbers and those we outsource are odd numbered. TIA for any help I can receive. I am a novice who is teaching himself VBA but this is way above my head.


Parts Data Export1.xlsm

[Paul_Hossler]

Simple approach (if I understood the requirements correctly)

Option Explicit


Sub PartNumbers()
    Dim rData As Range
    Dim i As Long
    Dim sPN As String, sPN1 As String, sPN2 As String
    
    Application.ScreenUpdating = False
    
    Set rData = ActiveSheet.Cells(1, 1).CurrentRegion '   A1:D23
    
    For i = 2 To rData.Rows.Count                     '  rows 2 - 23
        sPN = rData.Cells(i, 1).Value               '   C3972
        sPN1 = Left(sPN, 1)                         '   C
        sPN2 = Mid(sPN, 2, 1)                       '   3
        Select Case sPN1
            Case "0" To "9"
                rData.Cells(i, 4).Value = "Invalid Part Number"
                rData.Cells(i, 4).Interior.Color = vbYellow
            
            Case Else
                
                Select Case sPN2
                    Case "0", "2", "4", "6", "8"
                        rData.Cells(i, 4).Value = "In-House"
                    Case "1", "3", "5", "7", "9"
                        rData.Cells(i, 4).Value = "Outsource"
                End Select
        End Select
    Next i
    Application.ScreenUpdating = True
    
    MsgBox "Done"
    
End Sub

[jpitcairn]

It worked!!! Thank you!!