Formula to give the date another cells data changed?

[andrew_milonavic]

Hi,

If I have a formula in A1, is it possible to have a formula in B1 that would tell me the date A1 changed? So if A1 was Cat and the workbook/sheet recalculated and A1 stayed cat then the date in B1 wouldn't change. If A1 changed from cat to dog, or anything other than cat, then B1's date would update.

Possible?

Thanks

Andrew

 

[Scott Huish]

Not with a formula, but you could do that with VBA.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Set c = Intersect(Range("A1"), Target)
If c Is Nothing Then Exit Sub
Application.EnableEvents = False
    If UCase(c) <> "CAT" Then Range("B1") = Date
Application.EnableEvents = True
End Sub

Copy the code above.
Right click on the sheet tab where you want this to happen on.
Click View Code.
Paste into white area.
Hit Alt-q
Save the workbook as type .xlsm

 

[andrew_milonavic]

Hey Scott,

Thanks for the suggestion, was really hoping for a formula solution, but if it' not possible I'll test your VBA.

Thanks

Andrew