CODEWITHSUNDEEP
excelvba
Raj
Raj

Reputation: 35

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

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..

Upvotes: 2

Views: 1198

Answers (1)

VBasic2008
VBasic2008

Reputation: 54983

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

Upvotes: 2

Related Questions