help with worksheet change event

  • Thread starter Thread starter Mike NG
  • Start date Start date
M

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
 
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
 
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
Click to expand...
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
 
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
 
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
Click to expand...
I've just remembered :- Application.EnableEvents = False is what I need
 
Back
Top