Date Stamp On Worksheet Change

**SamuelT** · 04-23-2009, 10:35 AM

Hi all,

I have the following little piece of code that adds the date to C3 whenever the workbook is saved:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   
   Sheets("Resourcing").Range("C3").Value = Now()
   
End Sub

Can anyone advise on how (if it is at all possible) to only run this code when someone changes a cell value (in particular the cells in range A7:AY186 and A189:AY216)?

TIA,

SamuelT

**dominicb** · 04-23-2009, 11:03 AM

Good afternoon SamuellT

Originally Posted by SamuelT

Can anyone advise on how (if it is at all possible) to only run this code when someone changes a cell value (in particular the cells in range A7:AY186 and A189:AY216)?

You mean run the instant it changes, and doesn't check on saving? If so, something like this should help :

Private Sub Worksheet_Change(ByVal Target As Range)
Set int1 = Application.Intersect(Range("A7:AY186"), Range(Target.Address))
Set int2 = Application.Intersect(Range("A189:AY216"), Range(Target.Address))
If Not int1 Is Nothing Or Not int2 Is Nothing Then
Sheets("Resourcing").Range("C3").Value = Now()
End If
End Sub

HTH

DominicB

**SamuelT** · 04-23-2009, 11:29 AM

Hi dominicb,

Thanks for that. Just needed to declare the variables and all is working well.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim int1 As Range
Dim int2 As Range

Set int1 = Application.Intersect(Range("A7:AY186"), Range(Target.Address))
Set int2 = Application.Intersect(Range("A189:AY216"), Range(Target.Address))
If Not int1 Is Nothing Or Not int2 Is Nothing Then
Sheets("Resourcing").Range("C3").Value = Now()
End If
End Sub

Much obliged!

SamuelT