Have the code ignore the word INVALID & TBA

[ipbr21054]

Morning,
I am using the code supplied below.
Most often a date will be in the cell but for first customers there will be the word NEVER or TBA

My issue is that if i click in a cell that has NEVER or TBA i see the error message INVALID PROPERTY VALUE
I understand its doing this because its not a date but can the code be edited to ignore the word NEVER & TBA etc

When i then debug i see the line selected below in yellow.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' THIS WILL COLOUR ACTIVE CELL & KEEP INTERIOR COLOUR ONCE LEFT HAS BEEN LEFT
    Dim myStartCol As String
    Dim myEndCol As String
    Dim myStartRow As Long
    Dim myLastRow As Long
    Dim myRange As Range

    If Target.Cells.Count > 1 Then Exit Sub
    
    Application.ScreenUpdating = False
    
'   *** Specify columns to apply this to ***
    myStartCol = "A"
    myEndCol = "K"

'   *** Specify start row ***
    myStartRow = 7
    
'   Use first column to find the last row
    myLastRow = Cells(Rows.Count, myStartCol).End(xlUp).Row
    
'   Build range to apply this to
    Set myRange = Range(Cells(myStartRow, myStartCol), Cells(myLastRow, myEndCol))
    
    '   Check to see if cell selected is outside of range
    If Intersect(Target, myRange) Is Nothing Then Exit Sub
    
    With Target
        .Worksheet.Cells.FormatConditions.Delete
        .FormatConditions.Add xlExpression, , True
        .FormatConditions(1).Interior.Color = vbWhite
    End With
    
    With Sheet7.DTPicker1
    .Height = 40
    .Width = 40
    If Not Intersect(Target, Range("G7:H40")) Is Nothing Then
      .Visible = True
      .Top = Target.Top
      .Left = Target.Offset(0, 1).Left
      .LinkedCell = Target.ADDRESS
    Else
      .Visible = False
    End If
  End With
      
End Sub

 

[Alex Blakenburg]

If you don't want it to run any of the code if it is not a valid date, then just change your first IF Statement to this:-

    '   Check to see if cell selected is outside of range and if a valid date
    If Intersect(Target, myRange) Is Nothing Or Not IsDate(Target) Then Exit Sub

 

[ipbr21054]

Hi,
I then see that message and Run Time Error 5 all the time now

 

[Alex Blakenburg]

Which line is highlighted when you click on debug ?
And can you give an XL2BB sample of your data ?
PS I am in Australia and login off now

 

[Alex Blakenburg]

I don't have not used date picker and can't test your code.
Your code has some differences to what @DanteAmor has posted on this Forum at the link below.
I suggest try to match his code. I suspect the formatted value line may be significant.
VBA Coding - Excel keeps crashing

 

[Michael M]

Maybe this

    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Value = "NEVER" Or Target.Value = "TBA" Then Exit Sub
    Application.ScreenUpdating = False

 

[ipbr21054]

Perfect that worked Michael M

Thanks

 

[Michael M]

Glad to help?