updating date every time particular cells' contents are changed

[nazatron]

Hi,

I am working on a spreadsheet where I wish to have a column B where each cell shows the date on which the cell in the corresponding row of column A is manually updated by me.

In other words,

<table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="484" height="128"><col style="width: 48pt;" width="64"> <col style="width: 69pt;" width="92"> <col style="width: 84pt;" span="2" width="112"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" height="20">
</td> <td style="width: 69pt;" width="92">A</td> <td style="width: 84pt;" width="112">B</td> <td style="width: 84pt;" width="112">C</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">1</td> <td> STATUS</td> <td>DATE PAUSED</td> <td>DATE ACTIVATED</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">2</td> <td> paused</td> <td>march 15,2010</td> <td>march 13,2010</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">3</td> <td> active</td> <td>march 14,2010</td> <td>march 15,2010</td> </tr> </tbody></table>

kind of thing if for instance I typed in "active" in one and "paused" in one today (march 15). I am updating the status of campaigns.

I attempted achieving this by typing in B2 and dragging it down:
=IF(A2="paused",NOW(),B2)
which yields a warning message regarding circular reference (because I am saying that unless the cell content becomes "paused", it returns what it already had in it, in other words the date from the last time it was paused.
Despite the warning, this has been working fine UNTIL I refreshed the data (the above table is a snippet of a huge pivot table which updates from a query). When I refresh the data, it inserts in the date cells today's date.

Ultimately, I need to have cells that update properly so we can keep a record of previous activity.
Did I make myself clear? I would really appreciate some guidance here. Thank you!!

Naz

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
    Application.EnableEvents = False
    Select Case LCase(Target, Value)
        Case "paused": Target.Offset(, 1).Value = Date
        Case "active": Target.Offset(, 2).Value = Date
    End Select
    Application.EnableEvents = True
End If
End Sub

then press ALT + Q to return to your sheet.

 

[nazatron]

I did what you said but nothing seems to have changed. Am I missing something?

Thanks
naz

 

[VoG]

Are macros enabled?

 

[nazatron]

Yup.

 

[nazatron]

Is there another way to do it? Thanks,

naz

 

[VoG]

Sorry, stupid typo in my code. Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
    Application.EnableEvents = False
    Select Case LCase(Target.Value)
        Case "paused", "active": Target.Offset(, 1).Value = Date
    End Select
    Application.EnableEvents = True
End If
End Sub