Runtime Error 13 - Type Mismatch

Hi,

I am new to VBA and have been mashing together lines of code from different sites to get the result I need. It is rough.

I think I have got it all working so far except for when I try and delete multiple items from one column (which are dropdown selections). The problem occurs only in this column and nowhere else. Everything is locked down so users can't see or adjust anything outside of what I have left open.

Code is pasted below. Any help for this would be great!

When I try delete 2 or more selections in one go from column AE the runtime error 13 appears. The thing this piece of code is achieving is a time stamp for when the status of work changes.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rInt As Range
    Dim rCell As Range
    Dim tCell As Range
    

    Set rInt = Intersect(Target, Range("G4:G2503"))
    If Not rInt Is Nothing Then
        For Each rCell In rInt
            Set tCell = rCell.Offset(0, 37)
            If IsEmpty(tCell) Then
            ActiveSheet.Unprotect Password:="password"
                tCell = Now
                tCell.NumberFormat = "mm/dd/yy hh:mm AM/PM"
            ActiveSheet.Protect Password:="password", AllowFiltering:=True, AllowFormattingCells:=True
            End If
       
        Next
   
    End If
   
    
    With Target
     
        If .Column <> 31 Or .Row < 3 Then Exit Sub
        If .Value = "In Progress" Then
          ActiveSheet.Unprotect Password:="password"
            If .Offset(0, 14).Value = "" Then
                .Offset(0, 14).NumberFormat = "mm/dd/yy hh:mm AM/PM"
                .Offset(0, 14).Locked = True
                .Offset(0, 14).Value = Now
            End If
             ActiveSheet.Protect Password:="password", AllowFiltering:=True, AllowFormattingCells:=True
        ElseIf .Value = "Complete" Then
          ActiveSheet.Unprotect Password:="password"
            If .Offset(0, 15).Value = "" Then
                .Offset(0, 15).NumberFormat = "mm/dd/yy hh:mm AM/PM"
                .Offset(0, 15).Locked = True
                .Offset(0, 15).Value = Now
            End If
               ActiveSheet.Protect Password:="password", AllowFiltering:=True, AllowFormattingCells:=True
        ElseIf .Value = "Cancelled" Then
          ActiveSheet.Unprotect Password:="password"
            If .Offset(0, 16).Value = "" Then
                .Offset(0, 16).NumberFormat = "mm/dd/yy hh:mm AM/PM"
                .Offset(0, 16).Locked = True
                .Offset(0, 16).Value = Now
            End If
             ActiveSheet.Protect Password:="password", AllowFiltering:=True, AllowFormattingCells:=True
        ElseIf .Value = "On Hold" Then
          ActiveSheet.Unprotect Password:="password"
            If .Offset(0, 17).Value = "" Then
                .Offset(0, 17).NumberFormat = "mm/dd/yy hh:mm AM/PM"
                .Offset(0, 17).Locked = True
                .Offset(0, 17).Value = Now
            End If
            ActiveSheet.Protect Password:="password", AllowFiltering:=True, AllowFormattingCells:=True
      End If
      
    End With
ActiveSheet.Protect Password:="password", AllowFiltering:=True, AllowFormattingCells:=True
End Sub

Try putting this on top of code.

If target.count > 1 then exit sub

Instead of .Value, try .Text.

If the cell that's just been changed has an error like #N/A you will get that error.

In future, please use code tags around your code. I have done it for you on this occasion.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.