Type mismatch

[dpaton05]

When I open my spreadsheet and delete contents out of a cell, I get a type mismatch error. This is my code:

Private Sub Worksheet_Change(ByVal Target As Range)
'ActiveSheet.Unprotect
        Dim ans As String
        If Not Intersect(Target, Range("A:A,B:B")) Is Nothing Then
        Application.EnableEvents = False
        Select Case Target.Column
            Case Is = 1
                'If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
                If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then
                    Application.EnableEvents = True
                    Exit Sub
                End If
                
                
                If Target.Value < Date Then
                    If MsgBox("This input is older than today !....Are you sure that is what you want ???", vbYesNo) = vbNo Then
                        Target.Value = ""
                    End If
                End If
            Case Is = 2
       [U]         If Target = "Activities" Then[/U]
                    Do
                        ans = InputBox("Please enter the Activities cost." & _
                        vbCrLf & "************************************" & vbCrLf & _
                        "To change an activity cost, select Activities from the Service list again.")
                        If ans <> "" Then
                            Cells(Target.Row, "N") = ans
                            Exit Do
                        Else
                            MsgBox ("You must enter a Activities cost.")
                        End If
                    Loop
                End If
            
        End Select
        End If
    Application.EnableEvents = True
'ActiveSheet.Protect
End Sub

I have underlined the line that gets highlighted. Could someone please help me find with what might the causing the problem as I don't know.

Thanks

 

[Michael M]

Try using

If Target.value = "Activities"

 

[DanteAmor]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'ActiveSheet.Unprotect
  Dim ans As String
  'I suggest this go to the start before turning off the events.
[COLOR=#0000ff]  If Target.Count > 1 Or IsEmpty(Target) Then Exit Sub[/COLOR]
  '
  On Error GoTo App_Events
  If Not Intersect(Target, Range("A:A,B:B")) Is Nothing Then
    Application.EnableEvents = False
    Select Case Target.Column
      Case 1
        If Target.Value < Date Then
          If MsgBox("This input is older than today !....Are you sure that is what you want ???", vbYesNo) = vbNo Then
            Target.Value = ""
          End If
        End If
      Case 2
[COLOR=#0000ff]        If LCase(Target.Value) = LCase("Activities") Then[/COLOR]
          Do
            ans = InputBox("Please enter the Activities cost." & _
              vbCrLf & "************************************" & vbCrLf & _
              "To change an activity cost, select Activities from the Service list again.")
            If ans <> "" Then
              Cells(Target.Row, "N") = ans
              Exit Do
            Else
              MsgBox ("You must enter a Activities cost.")
            End If
          Loop
        End If
    End Select
  End If
App_Events:
  Application.EnableEvents = True
  'ActiveSheet.Protect
End Sub

 

[dpaton05]

Thanks for the reply guys, I will try those answers when I get back to work on Friday.

 

[dpaton05]

Thanks Dante, your answer worked.

 

[DanteAmor]

Im glad to help you. Thanks for the feedback.