Update a sheet colmun value based on another sheet column value get changed

1
votes

I was trying to update modified/value entered time in a column's cell of Sheet2 in a workbook where in Sheet1 if a specific column's cell value get changed.

I'm using the below code which is not working, did i missed anything?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("Timesheet[Start Time]")) Is Nothing Then
        
        Sheet2.Cells(Target.Row, 1).Value = Now()
        
    End If
End Sub

Hope this is possible..

Please help me out.. Thanks in advance :)

2
excelvba
You do not want the Worksheet_SelectionChange event but the Worksheet_Change event - Scott Craner
If the Sheet1 cells are in 1 to 1 correspondence with the Sheet 2 cells, then it might be simpler to just add a formula in the Sheet 2 cells that refer to the Sheet1 cells. - PKatona
Thanks Scott but Worksheet_Change event also not working :( - Linga
@Linga, if the user toggles between blanks and a value, then the time will get updated, but if the value in Sheet1 cell 1 remains static, the value inserted by the formula is not changed. - PKatona
One) is this event on sheet1? two) try changing this line Sheet2.Cells(Target.Row, 1).Value = Now() to Sheets("Sheet2").Cells(Target.Row, 1).Value = Now() - Scott Craner

2 Answers

0
votes

Putting this in sheet2 cell a1 will do what you want. Of course you'll have to populate all of the cells in sheet 2 with the formula, and you'll have to format it to whatever date format you want, but it'll work:

=IF(Sheet1!A1<>"",NOW(),"")
0
votes

Thank you so much scott for the help :)

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("Timesheet[Start Time]")) Is Nothing Then
  Sheets("Sheet2").Cells(Target.Row, 1).Value = Now()                 
  End If
End Sub