How can I use an "if" statement or some other way to calculate a formula when a cell value changes? Something like: =if(A1 changes value,(today), no change) This formula would reside in cell C1 and would display todays date in cell C1 if the value in A1 has changed today. If it has not changed today, it would leave the previous date (whatever it would happen to be from the last time the value in A1 has changed) in the cell. The value in A1 would be changed by typing the value directly in A1. This sounded simple.....but now I'm can't find a function to detect the change in value.....any help out there please?

Thank you.....Melinda

Something happenned....I wanted to reply to the person who replied to me, and somehow I lost the post. I am new to VBA and I can't get the code to work. The cells that are changing value are in column G. The current date is in column K. I want the date in column K to change as the value in column G changes.....or put the new date in column L. The rows are 3 through 2950. How do I use the code with that range?
Thank you,
Melinda

Sorry about that, I discovered my mistake and thought I could quietly delete the post before someone read it! Try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 And (Target.Row > 2 And Target.Row < 2351) Then
Cells(Target.Row, 11) = Now
End If
End Sub

That should do the trick.

Originally posted by bmacr [/i]
Sorry about that, I discovered my mistake and thought I could quietly delete the post before someone read it! Try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 And (Target.Row > 2 And Target.Row < 2351) Then
Cells(Target.Row, 11) = Now
End If
End Sub

That should do the trick.


No problem....thought I just messed things up. That works execpt it puts the date in one cell only. I want it to put the date in the corresponding row with the value change. If the values changed on row 1100, I want the date to appear in row 1100.

Thank you!!!

Hmm... on my machine if I change a value in G20, today's date appears in K20. What cell does it appear in on yours?

got it now...had a typo. I put column instead of row in that last bit. Thank you so much!!!!

My pleasure. Glad you got it working.