update date in exce ONLYl when one or more cells are modified

[pertusati]

Hi all,
My name is Fabrizio and I am relatively new in the excel world.
What I would like to do is build a sort of inventory of chemicals in my lab.
I have prepared an excel sheet with four columns:
A) name of the chemical; B amount, C) reference number and D) location, plus two rows at the beginning of each page: one with the date and another one with the description of the content of the cells below.
What I would like to do is insert in one of the initial row the date the inventory is modified. In other words, when I will introduce a new chemical or when the amount of one already into the inventory is modified ONLY at that time the date will change. In this way, I will know when the last modification occur. If I am using the function TODAY every time I open the file the date automatically is updated and I do NOT whant this.
I know it looks complicated, but anyone knows if it is possible to do this?
Thanks in advance for any help provided.
Fabrizio

 

[JLGWhiz]

Let's say that column E will indicate a date of last activity for an item, based on quantity changes and additions of new items. Deletions, of course, would not be necessary to show. You can use a Worksheet_change event procedure to show the date for a given item. Copy this procedure to your worksheet code module by right clicking the sheet name tab, then click "View Code" in the pop up menu and paste the code into that window. It will then run automatically when any change is made to the sheet, but will only execute the date entry if the change is made in column B.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Range("E" & Target.Row) = Format(Date, "ddmmmyyyy")
End If
End Sub
Code:

 

[pertusati]

Let's say that column E will indicate a date of last activity for an item, based on quantity changes and additions of new items. Deletions, of course, would not be necessary to show. You can use a Worksheet_change event procedure to show the date for a given item. Copy this procedure to your worksheet code module by right clicking the sheet name tab, then click "View Code" in the pop up menu and paste the code into that window. It will then run automatically when any change is made to the sheet, but will only execute the date entry if the change is made in column B.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Range("E" & Target.Row) = Format(Date, "ddmmmyyyy")
End If
End Sub
Code:

Thanks very much for this hint. However since i am not an expert in excel... were I can find the sheet code module? if I right clic on the sheet name (bottom of the page the menu appear but do not contain the view code menu.
I apologize for this lack of skills.
P.S I am using excel 2011 for MAC.
Fabrizio
Apologize in advance for this lack of skills.