Add Date Stamp when cell if modified and delete if content is cleared

**nicholas.robinson** · 07-31-2012, 12:24 PM

I need some help.

I am trying to use an event Macro, so that if a cell is entered with "Y" a date stamp will appear to the left of the cell any where within the worksheet. I also need to be able to clear the date stamp if the contents of modified cell are cleared. Below is my unsuccessful attempt so far. Any ideas?

Please Login or Register  to view this content.

Thanks for any help!

**tigeravatar** · 07-31-2012, 01:05 PM

nicholas.robinson,

Welcome to the forum! In the future, please wrap your code in code tags. See forum rules (link in my sig) for how.
As to your query, you need to tell the code what range of cells to monitor. Right now, the code is looking at every single cell in the worksheet to see if it is a "Y" or was deleted. Also, you have the macro calling itself recursively because you enabled events and then performed the ClearContents, which is considered a worksheet change, so code will keep calling itself until it errors because there is no column to the left of column A.

So, basically, if you want to monitor column B from row 2 to row 500:

Please Login or Register  to view this content.

**nicholas.robinson** · 07-31-2012, 01:14 PM

I would actually like when certain columns are marked with "Y", i.e. if column F is marked with Y, a date stamp appears in column e; if column H is marked with Y, a date stamp appears in column g; etc. What would I need to change then.

**tigeravatar** · 07-31-2012, 01:28 PM

Please Login or Register  to view this content.

**Leith Ross** · 07-31-2012, 01:30 PM

Hello nicholas.robinson,

The only problem I see with your code is it is inserting the date and time. To insert only the time into the cell at the left, you can do it this way.

Please Login or Register  to view this content.

**tigeravatar** · 07-31-2012, 01:38 PM

Leith,

Isn't that code missing an End If after the Application.EnableEvents = True?
Also, doesn't my earlier point about the recursive calling still stand? I just tested the code and both problems arose immediately.