Make cell entry event change another cell?

[Ken]

Hi.

When a particular value is entered into one cell, I want to cause
another value to be set into a different cell. This different cell is
in the same row at an offset.

I wrote a little macro to cause an offset from the active cell to get
set, but I don't know how to cause this macro to run when the value is
changed in the "trigger" cell.

Specifically, I have a little "to-do" spreadsheet. One column
indicates completion status with a "Y" or "N". Another column
indicates completion date. So when the status changes to "Y", I want
that event to run the macro that will set the completion date to
Today().

Any help would be greatly appreciated!

Thanks,

Ken

P.S.: I am running Excel 97.

 

[Bob Phillips]

Ken,

This worksheet event code checks for a Y being input in column B, and puts
the date in column F

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 2 Then
If UCase(Target.Value) = "Y" Then
Target.Offset(0, 4).Value = Format(Date, "dd mmm yyyy")
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

To enter it, right click the sheet tab, select 'View Code' from the menu,
and paste the code into the code pane shown.

To change column B, change
If Target.Column = 2 Then
to the appropriate column number.

To change the date column from F, change the 4 in
Target.Offset(0, 4).Value
to the number of columns to the right.

 

[Ken]

Thanks. Worked great!

One follow-up question:

I wanted to cause a default value to be set in one of the cells when a
new row is entered. Specifically, I want to set the cell in the
Completed column to be "N".

How would that be done?

Thanks so much,

Ken