Calculation change date time stamp

Please please please help! I'm trying to stay away from circular references, so I need a VBA code that can formulate the date a cell changed.

For example, Column D changes by a formula for "Does not need repair" to "Needs repair". I need Column E to show the date cell D1 changed from "Does not need repair" to "Needs repair". I also need to know how to make the date remain static and then go away when the cell is back to "Does not need repair".

Thanks in advance!

Originally Posted by mehmetcik

This needs a simple sheet specific Macro

I will write one for you.


Right Click on the sheet name at the bottom of excel and select view code.

Paste this code into the window that opens and then close it.

Please Login or Register  to view this content.

Thank you so much @mehmetcik! For some reason the code isn't running. Is it because the "Needs repair and "Does not need repair" are populated by a formula?

Originally Posted by JBeaucaire

Circular references are THE way to do this via formula.

With VBA, you have to check the entire sheet EVERY time any formula on the sheet calculates a change. I'm not sure this is better at all, but it is VBA. This event macro goes in the sheet module:

Please Login or Register  to view this content.

Yours is pulling a date, but then it crashes my excel I already have some VBA codes on other sheet's module. I'm not if they are interfering or what's happening

Originally Posted by mehmetcik

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I've attached an example workbook.

Hi,

The circular reference sometimes returns an error. The workbook book will go back to default settings and not enable iterative calculations. Then the file either freezes/locks up or I lose the previous dates that had been calculated before.

Originally Posted by JBeaucaire

Try this version:

Please Login or Register  to view this content.

Ah thank you!! It works! But excel keeps crashing when it runs. Is there supposed to be some sort of error statement in the code? I don't understand why excel is crashing now. (Sorry, I'm pretty new with VBA)

What I'm seeing is that code isn't working with my conditional formatting..

I was actually able to get it to work. Thank you for your help!