Update cell value when another cell is activated

[UglyBunny]

Hello Sirs, Madams, super intelligent shades of the colour blue.

I have a spreadsheet of orders, the 'order status' column has a list of possible order statuses, whenever I change one I manually update the date next to it.
Is there a magic formula to get the date to update automatically whenever I change the value in the order status cell?

<TABLE style="WIDTH: 104pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=139><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 width=64>Order status</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=75>Date</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Submitted</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>10/08/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Pending</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>11/08/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Complete</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>12/08/2011</TD></TR></TBODY></TABLE>

Thanks for looking,

 

[Trevor G]

Try soemthing like this

Select the Sheet Name and then right mouse button, then in the VBA Screen add this in

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><SPAN style="color:#00007F">If</SPAN> Target.Cells = "Submitted" <SPAN style="color:#00007F">Or</SPAN> Target.Cells = "Pending" <SPAN style="color:#00007F">Or</SPAN> Target.Cells = "Completed" <SPAN style="color:#00007F">Then</SPAN><br>ActiveCell.Offset(0, 1) = <SPAN style="color:#00007F">Date</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[UglyBunny]

Many thanks Trever,

I've done as suggested however nothing happens when I update the cell value.
Maybe I'm doing something wrong; I've only ever coded macros that are button ran. Am I right in thinking this is running automatically on my worksheet all the time?

 

[Weaver]

Could be a case issue. Does the cell say "Pending" or "pending"?

Include the line

Option Compare Text

before the subroutine definition, see if that helps.

 

[UglyBunny]

Still no joy.. Thanks for your input though

 

[Weaver]

Try this instead

 Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo reEnableEvents
    If Target.Cells = "Submitted" Or Target.Cells = "Pending" Or Target.Cells = "Completed" Then
        Application.EnableEvents = False
        Target.Offset(0, 1) = Date
    End If
reEnableEvents:
    Application.EnableEvents = True
End Sub