VB works only when value is typed into cell, not when formula updates cell value

[dwiseman]

Greetings,

I have this VB code that is running on my worksheet. What I'm attempting to do here is insert 'Date' in the adjacent cell (Column J) when the word "Delivered" is updated into the target cell (Column I). This is working in part. When I focus on a cell in column I and type in the word "Delivered", the adjacent cell in column J gets the Date stamp. But my issue is that I am not typing in the word 'Delivered'. Instead, a formula is evaluating other cells in the worksheet and when all of those cells are marked as 'Complete' then the word 'Delivered' is filled into the cell in column I. My problem here is that when the formula inserts 'Delivered', then the offset cell (Column J) does not get the Date.

How can I get around this issue? Or is there another way that I should be populating this date? I discovered that I cannot simply use a formula in the worksheet because the date keeps dynamically changing, so that's why I went the VB route.

Here's my VB Code:

Please Login or Register  to view this content.

The other issue is that if the word 'Delivered' is removed from column I, I want the date in the adjacent cell to be cleared. I would also like to limit this VB code to 'Delivered' in column I only if at all possible.

Sorry for the questions but I'm new to VB. Thanks so much for the help!
Regards,

Drew

[JBeaucaire]

I assume that the value in I2 would change to "Delivered" when other values in row 2 changed, correct? Each row is a separate set of data?

We can adjust your macro to check the current row anytime a value changes and change column "I" for that row only, then add/delete the date stamp as needed.

Please Login or Register  to view this content.

[mikerickson]

Perhaps something like this would work if all of the cell's precedents are on the same sheet.

Please Login or Register  to view this content.

[dwiseman]

Guys,

You are geniuses!
mikerickson - I never got to testing your solution, as the one proposed by JBeaucaire was exactly what I needed. Thanks to all of you for the help.

This forum is invaluable and I do appreciate it!

Best regards,
Drew

[dwiseman]

Guys,

One small thing I did notice with this solution provided by JBeaucaire is that it applies to the entire column. I have mutliple headings and the data starts at Row 11 so I really only want this code to apply to rows 11 and below. My biggest concern is that I don't want values in rows 1-10 to be blanked out when the word Delivered is not present. I tried to plug in a range but had no luck getting that to work.

Any idea on that?

Thanks so much,
Drew

[JBeaucaire]

Here you go:

Please Login or Register  to view this content.

[dwiseman]

Beautiful! Thanks for the insight. Works like a champ!

Drew