0

I have been filling out an excel sheet logging project information. I would like to know how to have to populate a cell with the current date when I update cells in the adjacent row in the same sheet. There is currently information in the adjacent cells but I want it to hold the date that I previously updated it with. Only when I update the information I want it to change

I have googled for logical operator information but I cannot seem to find a direct answer. If there is a VB macro I could I am open to any solutions. I have found information on the TODAY() function in excel as well but I do not know how to apply it my particular problem. Any references to VB macros or information would be helpful. Thank you.

Community
  • 1
  • 1
Chris Wakoksi
  • 51
  • 1
  • 7

1 Answers1

1

Try this, Alt-F11 to launch the editor, right-click "Sheet1" (or your sheet name) and select add code, paste in something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    Set KeyCells = Range("B2:B10")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        Cells(Target.Row, Target.Column - 1) = Now()
    End If
End Sub

If you type something into B2 to B10 then the adjacent column is updated with the current date.

Richard Hansell
  • 5,315
  • 1
  • 16
  • 35
  • ...and note this STILL WORKS when you save and reopen your Excel workbook, unlike the previous answer. – Richard Hansell Oct 20 '14 at 16:22
  • 1
    Yup that will do. You can replace `Range(Target.Address)` with just `Target`. – Siddharth Rout Oct 20 '14 at 16:23
  • Just one more note. One will have to add other checks to see if the data was `logged` and not deleted or as a matter of fact check if the user just didn't double click and exit. Because even in these scenarios the above code will fire. And while we are at it, [THIS](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) is worth a read. – Siddharth Rout Oct 20 '14 at 16:33
  • It gives me an error saying "argument not optional" – Chris Wakoksi Oct 20 '14 at 18:25
  • Also worth noting that if the user updates a range of cells which includes cells in B2:B10 but *also cells outside of that range* then all of those cell changes will be logged, not just those in B2:10.... – Tim Williams Oct 20 '14 at 19:48
  • Would it make a difference if I have centered and merged the row cells that have the date in them? – Chris Wakoksi Oct 20 '14 at 20:10
  • Formatting date cells shouldn't matter, which line of the script raises the error? Regarding the other comments, yes, there are some holes in this process that you could certainly code around, e.g. updating two cells at once. However, the script I put together is just a starting point and I wanted to keep it as simple as possible. – Richard Hansell Oct 21 '14 at 11:42