Timestamp Issues

[AudBall]

Hi MrExcel community,

I've used the following code a few times to get a timestamp when a range of noncontiguous cells are populated. However, now I'm trying to get the timestamp to clear if any one of the populated cells within the range is cleared. Currently, the timestamp only clears if when the first cell in the range is cleared. My understanding is that vbEmpty doesn't work with noncontiguous ranges. Unfortunately, my attempts to use Boolean values and assigning it to a variable have proven unsuccessful.

Any assistance would be greatly appreciated.

Thank you.

Private Sub Worksheet_Change(ByVal target As Range)
Dim MyRange1 As Range
Dim MyDate1 As Range
Dim MyUser1 As Range

    Set MyRange1 = Range("F10, F12, F18, F20, G10, G12, G14, G16, G18, G20, H10, H16, I10, I14, I20, J10, K18:K20")
    Set MyDate1 = Range("F23")
    Set MyUser1 = Range("F22")
        
     If Not Intersect(target, MyRange1) Is Nothing Then
         
         If MyRange1 = vbEmpty Then
            'ActiveSheet.Unprotect Password:="12345"
            MyDate1.ClearContents
            MyUser1.ClearContents
            'ActiveSheet.Protect Password:="12345", DrawingObjects:=True, Contents:=True, Scenarios:= _
            True
            'ActiveSheet.EnableSelection = xlUnlockedCells
         End If
         
         For Each cel In MyRange1
            If cel = "" Then Exit Sub
         Next cel
            'ActiveSheet.Unprotect Password:="12345"
            MyDate1 = Date & " " & Time
            MyUser1 = Application.UserName
            'ActiveSheet.Protect Password:="12345", DrawingObjects:=True, Contents:=True, Scenarios:= _
            True
            'ActiveSheet.EnableSelection = xlUnlockedCells
     
     End If
  
        
End Sub

 

[mumps]

Replace:

If MyRange1 = vbEmpty Then

with

If target = "" Then

 

[AudBall]

/sigh ... Thank you.

 

[mumps]

You are very welcome.

 

[AudBall]

So, that did fix the initial issue. However, if I delete the contents of multiple cells at the same time, I get a Run Time Error: 13 - Type Mismatch on

If target = "" Then

.

 

[AudBall]

Fixed it with:

If target.Cells(1).Value = "" Then