For reference:

Device- MacBook; I normally work with windows. I haven't had any issues with compatibility.

OS- Big Sur v11.5.2

Excel Ver.- 16.60

File Type- xlsm

Operation detail:

I need Column Range D4:D26 to Input date stamp when any value is input to corresponding cells in column C.

Problem:

I have this code-

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim EntryDate As String
    Dim rngA As Range  'Declared variable
    
    Set rngA = Range("D:D") 'Set Declared variable
    
    
    If Intersect(Target, rngA).Value <> "" Then 'This line is displaying the runtime 91 code
    EntryDate = Format(Now, "dddd, mmm dd, yyyy")
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = EntryDate 'When I comment this line out I get no runtime errors
    
    End If
End Sub

• When I input value in column C my expected results happen. Column D does output the date stamp. But also returns the runtime error 91.

Additional Notes: I have been to multiple forums and many related questions within this platform. Tried utilizing new workbooks and worksheets; I have also tried specifying the current workbook and worksheet for the variable object and still a runtime 91 returns at the same problematic line. What is it about my Intersect syntax am I doing wrong? Is it the <> that is causing it?

CodePudding user response：

Two immediate issues:

• You need to test If Not Intersect(Target, rngA) Is Nothing first. That is, you need to test whether Target and column D intersect, before you attempt to use .Value.
• You're modifying the worksheet inside the Worksheet_Change event handler, causing the event to fire again. Normally one uses Application.EnableEvents = False to avoid this.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Intersect(Target, Me.Range("D:D"))

    If Not rng Is Nothing Then
         Dim entryDate As String
         entryDate = Format$(Now, "dddd, mmm dd, yyyy")

         one rror GoTo SafeExit
         Application.EnableEvents = False

         Dim cell As Range
         For Each cell In rng
             If Not IsEmpty(cell) Then
                cell.Offset(,1).Value = entryDate
             End If
         Next
    End If

SafeExit:
    Application.EnableEvents = True
End Sub