Running code in Range EXCEPT if specific values are found in cell.

[jmpatrick]

Hello, again. My Worksheet_Change includes code a macro that runs when a value is entered in a cell in a Named Range. I'm trying to have the code NOT run if the cell's value is either a single blank space (" ") or contains a forward slash as part of the value ("*/*").

Here's the section...

    Set KeyCells = Range("CalendarFloorsOrderNumberColumn")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
    
    Application.EnableEvents = False
                   
    Call FloorOrderNumber
        
    Application.EnableEvents = True
    
    End If

Here's what I tried without success:

    Set KeyCells = Range("CalendarFloorsOrderNumberColumn")
    Set Target = Application.Intersect(KeyCells, Range(Target.Address))
    If Target.Value = " " Then
    GoTo SkipBlank
    End If
    If Target.Value = "*/*" Then
    GoTo SkipBlank
    End If
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
    Application.EnableEvents = False
    Call FloorOrderNumber
    Application.EnableEvents = True
    End If
    SkipBlank:

Here's the complete code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Or Target.HasFormula Or Target = "<ADD NEW>" Then Exit Sub

    On Error Resume Next

    If Not Intersect(Target, Range("ElevationColumn")) Is Nothing Then

        Application.EnableEvents = False

        Target = UCase(Target)

        Application.EnableEvents = True

    End If
    
    If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

    On Error Resume Next

    If Not Intersect(Target, Range("SubLotColumn")) Is Nothing Then

        Application.EnableEvents = False
        
        If Trim(Target) <> "" Then
        Target = Application.Substitute(Target, "..", ".")
        End If
        
        If InStr(1, Target, "-") > 0 Then
        Target = UCase(Target)
        End If

        Application.EnableEvents = True

    End If

    On Error GoTo 0

    Dim KeyCells As Range
        
    Set KeyCells = Range("DateColumn")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
    
    Application.EnableEvents = False
                   
    Call ChangeDate
        
    Application.EnableEvents = True
    
    End If
    
    If Not Intersect(Target, Range("DateColumn")) Is Nothing Then

    Application.EnableEvents = False

    Target = UCase(Target)

    Application.EnableEvents = True

    End If
         
    Set KeyCells = Range("CalendarFloorsOrderNumberColumn")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
    
    Application.EnableEvents = False
                   
    Call FloorOrderNumber
        
    Application.EnableEvents = True
    
    End If
 
End Sub

 

[jmpatrick]

I think I have the solution.

 

[jmpatrick]

This works:

    Set KeyCells = Range("CalendarFloorsOrderNumberColumn")
    If Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then Exit Sub
    If Target <> " " Or If Target <> "*/*" Then
        Application.EnableEvents = False
        Call FloorOrderNumber
        Application.EnableEvents = True
    End If

Now I work on the second condition (*/*)

 

[jmpatrick]

Tried this, but only the second condition ("*/*") works:

    Set KeyCells = Range("CalendarFloorsOrderNumberColumn")
    If Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then Exit Sub
    If Target <> " " Or Target <> "*/*" Then
        Application.EnableEvents = False
        Call FloorOrderNumber
        Application.EnableEvents = True
    End If

 

[jmpatrick]

Figured it out. Slight change:

    Set KeyCells = Range("CalendarFloorsOrderNumberColumn")
    If Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then Exit Sub
    If Target <> " " And Target <> "*/*" Then
        Application.EnableEvents = False
        Call FloorOrderNumber
        Application.EnableEvents = True
    End If