Date stamp +++ with overwrite protection

[lwhyatt]

I have a spreadsheet with 3 columns; Aroduct, Brice, C:date where from time to time I update the prices and I type in the date the price changed in the last column.

I want to automate the date stamping. However, I only want the date to be updated when the value in column B (price) is amended.

I'd like the existing records in column C to persist until there is a change in column B. In other words, I want to keep the existing values and only change the dates I have when the price in column B is changed, and otherwise leave the existing dates alone.

I've found many ways to add a timestamp but none that will keep existing values unchanged until a condition is met. The date will need to persist and not change each day I reopen the spreadsheet.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Change any price in column B and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    Target.Offset(0, 1) = Date
End Sub

 

[lwhyatt]

Thanks for the code, using Excel for Mac. Do I use Developer--> Visual basic and insert a new module? If so, not sure where to insert it.

 

[lwhyatt]

sorry got it. It works thanks!

 

[lwhyatt]

In the same spreadsheet I'd like to also monitor Range K:K and offset that date stamp by 2 columns. Tried the following but didn't work

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub
Target.Offset(0, 5) = Date
If Intersect(Target, Range("K:K")) Is Nothing Then Exit Sub
Target.Offset(0, 3) = Date
End Sub

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("E:E,K:K")) Is Nothing Then Exit Sub
    Select Case Target.Column
        Case Is = 5
            Target.Offset(0, 1) = Date
        Case Is = 11
            Target.Offset(0, 2) = Date
    End Select
End Sub

 

[lwhyatt]

Great, thankyou

 

[mumps]

You are very welcome.