VBA - For Each Loop - Cannot check all cells in range if first cell is not populated

Hi there,

I am relatively new to VBA and I am trying to create some validation rules for a data entry sheet end users will be populating.

I am using a For Each loop to verify the data in a particular column. The code below works if the cells are populated in order (top to bottom). However, if I copy and paste multiple incorrect values into the range at once, or if I populate a cell further down in the range leaving the top cell blank, the validation only works for the first cell in the range and does not loop through.

I cannot seem to make this work without being stuck in a continuous error loop. Is there a better way to do this?

Any advice would be appreciated!

Dim CCN As Range
Dim CCNEmpty As Boolean

For Each CCN In Range("G25:G34")

    If IsEmpty(CCN) = True Then
            CCNEmpty = True
            Exit For

    ElseIf Range("M14") = "Blank" Then
            MsgBox "Please select the business unit.", vbOKOnly
            CCN.ClearContents
            Range("F14").Activate
            Exit For

    ElseIf Range("M14") = 1 And Not (CCN Like "9###023###") Then
            MsgBox "Error! The format must be 9xxx023xxx.", vbOKOnly
            CCN.ClearContents
            CCN.Activate
            Exit For
        
    ElseIf Range("M14") = 2 And Not (CCN Like "9###002###") Then
            MsgBox "Error! The format must be 9xxx002xxx.", vbOKOnly
            CCN.ClearContents
            CCN.Activate
            Exit For
            
    ElseIf Range("M14") = 3 And Not (CCN Like "9###401###") Then
            MsgBox "Error! The format must be 9xxx401xxx.", vbOKOnly
            CCN.ClearContents
            CCN.Activate
            Exit For

    End If
    
Next CCN

Welcome to the board.

Please take a few minutes to read the Forum Rules, and then edit your post to wrap your code with CODE tags.

Thanks.

EDIT: Thank you.

I can't tell from your code what behavior you want; can you clarify?

Option Explicit
Public CCN  As Range 

Sub x()
    Dim CCNEmpty    As Boolean, _
        CCN_OK      As Boolean

    Select Case Range("M14").Value
        Case Is = "Blank"
            MsgBox "Please select the business unit.", vbOKOnly
            Range("F14").Activate
            Exit Sub
        
        Case Is = 1
            CCN_OK = Val_CCN("9###023###")
            If Not CCN_OK Then
                MsgBox "Error! The format must be 9xxx023xxx.", vbOKOnly
                CCN.ClearContents
                CCN.Activate
            End If
        
        Case Is = 2
            CCN_OK = Val_CCN("9###002###")
            If Not CCN_OK Then
                MsgBox "Error! The format must be 9xxx002xxx.", vbOKOnly
                CCN.ClearContents
                CCN.Activate
            End If
        
        Case Is = 3
            CCN_OK = Val_CCN("9###401###")
            If Not CCN_OK Then
                MsgBox "Error! The format must be 9xxx401xxx.", vbOKOnly
                CCN.ClearContents
                CCN.Activate
            End If
    End Select
End Sub
Function Val_CCN(ByVal Val_String As String) As Boolean
    For Each CCN In Range("G25:G34")
        If Not CCN Like Val_String Then
            Val_CCN = False
            Exit For
        End If
End Function

** No sample file = no check tsk, tsk, tsk