Update corresponding cell in a column based on row value using vba

Question

I enter data on sheet1 in various rows and columns. I am trying to get a date to display in sheet2 each time i enter a value in sheet1.

This is what i have in Sheet1:

`Sub Worksheet_Change(ByVal Target As Range)
 
 If Not Intersect(Target, Range("I7:NF1000")) Is Nothing Then
 Sheet2.Range("E7:E") = Now
 End If
End Sub`

The code works but the issue is that whenever a cell in Sheet1 range is updated it enters the date in all the cells in column E in Sheet2. How do i get it to only display date in the corresponding cell on sheet2. Eg. if i enter a value in I8 on Sheet1, i want the date to come across in E8 on sheet2. I can do it with each individual row but that would mean i would need to copy the If Not function a 1000+ times to cover all the rows.. see below:

`If Not Intersect(Target, Range("I8:NF8")) Is Nothing Then
 Sheet2.Range("E8") = Now
 End If
If Not Intersect(Target, Range("I9:NF9")) Is Nothing Then
 Sheet2.Range("E9") = Now
 End If`

and so on.... is there a way to avoid this and have a simpler code that will do this without me having 1000+ lines on code..

Answer 1

A Worksheet Change: Time Stamp in Another Worksheet

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
 
 Dim srg As Range: Set srg = Range("I7:NF1000")
 Dim irg As Range: Set irg = Intersect(srg, Target)
 If irg Is Nothing Then Exit Sub
 
 Set irg = Intersect(irg.EntireRow, Columns("E"))
 
 Dim TimeStamp As Date: TimeStamp = Now
 
 Dim arg As Range
 For Each arg In irg.Areas
 Sheet2.Range(arg.Address).Value = TimeStamp
 Next arg
End Sub

• Raj: Hi, Thank you for your prompt reply. The code seems to be working well. Just wondering if there is something I can add to the code to delete the date if I have made a mistake and want to delete an entry in Sheet1. The date currently stays there even if I delete the data entry. Also wanted to confirm, the date won't automatically update when the sheet is reopened the next day will it?
• VBasic2008: It is unclear what exactly you are asking. Best ask another question, add this code, and describe what it does and what it should be doing in more detail (the rules when it should clear the time). The times cannot be updated because they have been written as values (.Value = TimeStamp), not as formulas (.Formula = "=NOW()").
• Raj: No worries. Will start another question. Thank you for all your help.