help with worksheet change event

[Mike NG]

What i'd like to do is if a single cell in column I is changed, then in
column J of the same row I want to set the value to Date (today's date).
As a double check column B on the same row must also be populated

It's imperative drag and drop, and delete row events don't try and
populate column J

 

[Ron de Bruin]

Try this Mike

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Not Application.Intersect(Range("I:I"), Target) Is Nothing Then
If Target.Offset(0, -7).Value <> "" Then
Target.Offset(0, 1).Value = Format(Now, "mm-dd-yy hh:mm:ss")
End If
End If
End Sub

 

[Mike NG]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Not Application.Intersect(Range("I:I"), Target) Is Nothing Then
If Target.Offset(0, -7).Value <> "" Then
Target.Offset(0, 1).Value = Format(Now, "mm-dd-yy hh:mm:ss")
End If
End If
End Sub

Superb

Now I have two ways of updating my sheet - either manual input on the
sheet, or by some userforms I have on the page. Is there a way of
saying, "don't run the worksheet_change" event and turn it back on again
later

Cheers

 

[Ron de Bruin]

Hi Mike

You can use a cell on your worksheet for example with
Yes or No in it to turn it off.

You can use this as first line then in the change event

If Range("A1").Value = "No" Then Exit Sub

 

[Mike NG]

You can use a cell on your worksheet for example with
Yes or No in it to turn it off.

You can use this as first line then in the change event

If Range("A1").Value = "No" Then Exit Sub

I've just remembered :- Application.EnableEvents = False is what I need