Conditionally auto-update date by row

[ghubble]

Hi--can anyone help me with the following?

I want to automatically invoke the TODAY() function in Column P, by row, whenever anything in columns A-O is updated within each particular row.

It's for a spreadsheet where line items will be regularly updated over time, and I want to capture how fresh data are without manual user date updates.

Thanks for any pointers!

Gillian

 

[rs2k]

Hi Gillian, welcome to the board,

This should start you off, but (there's always a but!) it is dependant that Col's A:O are populated;

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:O")) Is Nothing Then
        Selection.End(xlToRight).Offset(0, 1).Value = Now
    End If
End Sub

Also, if more than one cell in a specific row is changed it will timestamp the next column (Q,R,S etc...)

I am curious on how to specify a specific column based on a changed cell. I don't think you can rely on the active cell method, because the user could tab, enter or click anywhere to submit the change, and as I said the offset method as it's flaws.

I will watch this post for an answer, until then, I'll keep trying,

Cheers
Colin

 

[Peter_SSs]

Gillian

Welcome to the MrExcel board!

Here is some alternative Worksheet_Change code to try. This may also not do exactly what you want, but see if it is close. I suggest that you test in a copy of your workbook.

To implement the code:

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Try making changes on your sheet.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> Changed <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> Changed = Intersect(Target, Columns("A:O"))<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Changed <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        Intersect(Changed.EntireRow, Columns("P")).Value = <SPAN style="color:#00007F">Date</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[rs2k]

Works for me

Peter,
Is there any reason you used 'Date' instead of 'Now'

whats the difference?

Colin

 

[Peter_SSs]

Peter,
Is there any reason you used 'Date' instead of 'Now'

whats the difference?

Colin

The OP referred to the worksheet function TODAY() [which returns the date], not the worksheet function NOW() [which returns date and time] so I used the vba function Date [which returns the date], not the vba function Now [which returns date and time].

 

[ghubble]

Colin and Peter,

Thanks so much! I will try these code suggestions and report back. I really appreciate you sharing your expertise, as I studied VBA for about a month back in 1998, which isn't helping me at the moment

Gillian

 

[ghubble]

I've been testing Peter's code to avoid Colin's multiple-column stamp caveat, and everything I've tried so far has achieved the desired result, an updated, single date stamp.

Thanks again to both of you for your help, and for my renewed primer on Visual Basic syntax. This piece of code is a huge time-saver!

Gillian