update timestamp if change_event for all column in row

**dainova** · 04-06-2012, 03:00 PM

Hi, all

I've found this super info by se1499
http://www.excelforum.com/excel-work...date-date.html
or
http://www.exceldigest.com/myblog/20...amp-in-a-cell/
that use this code to insert timestamp on the right of "A" cell.

Private Sub Worksheet_Change(ByVal Target As Range)
	Col = Left(Target.Address, 2)
	if Col = "$A" Then Target.Offset(0, 1) = Now
End Sub

It works only for any COLUMN A change, and use offset location for target cell (as I understand--)
How I can modify this code to make it work if specific list of cell in the single ROW has changed (e.g. B1..J1 ==> 10 cell row.
- and make timestamp also tobe in specific cell (let say in A1, or K1)

and to make it work for all rows. Can I do this this VB? or maybe use forms?

Appreciate your help, I'm trying to help one non-prof office to fill their food orders correctly, but I'm not that good in in VB, and they definetely can not go anywhere from Excell already provided to them by MS. THey just need to keep good log of all orders and their changes/cancellataions.

Tx to all
Dai

**Andrew-R** · 04-06-2012, 03:16 PM

It sounds like you're mixing up rows and columns. Your current code works on any cell in column A, are you saying that you only want the timestamp to be applied if certain rows in column A are changed? If so then something like this should work:

Private Sub Worksheet_Change(ByVal Target As Range)

Const sTARGET_CELL = "A1"
Const sVALID_COLUMNS = "A:A"

Dim avValidRows As Variant

avValidRows = Array(2, 5, 10)

If Not (Intersect(Target, Range(sVALID_COLUMNS)) Is Nothing) And Not (IsError(Application.Match(Target.Row, avValidRows, 0))) Then
  Application.EnableEvents = False
  Range(sTARGET_CELL).Value = Now()
  Application.EnableEvents = True
End If

End Sub

sTARGET_CELL controls where the timestamp is written, so you can change that to any cell you want to use.

Edited to add: And the line:

avValidRows = Array(2, 5, 10)

Controls which rows will update the timestamp. So it's currently set to stamp if cells A2, A5 or A10 change. If you've got quite a long list of rows then there may be easier ways to do this.

**dainova** · 04-06-2012, 07:04 PM

Thanks, Andrew.
Trying it right now.

Sorry if I mispresent my case..
I basically have order detail line in 10 columns for each row (A1,B1,....K1), let say 50 rows, one row for each order. THere is also header.
I put initial timestamp with iteration formula in A* column upon entering initial information for each row, but need to change this stamp or make another one on each row, if any column in detail row has been changed.

Best
Dai

**Mallycat** · 04-06-2012, 11:02 PM

Try this. This will update Column A with the current date/time based on any changes in columns B:J

Private Sub Worksheet_Change(ByVal Target As Range)
    Row = Target.Row
    Col = Target.Column
    If Col >= 2 And Col <= 10 Then
        Cells(Row, 1) = Now()
    End If
End Sub