Macro Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know very little about VBA coding. I found an event macro in another
thread on this board to enter a date/time stamp in a cell when a chanage was
made in a range of cells. The code was written for a range that was a single
column and used .offset(0,1) to determine where the date was entered (see the
macro below). I am trying to modify this macro for a three demensional range
(a1:h12) and want the date to be stored in the same row as the active cell
but in column I. I am sure this is a simple change but it is beyond my
knowlege - any help?

The following is the macro I am trying to modify:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

Rob
 
If I read your post you want the date in column "i"? if so here is a
mod otherwise you'll need to change the column letter in the "cells"
statements

Place in the sheet level module


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:H12")) Is Nothing Then
Application.EnableEvents = False
If Not IsEmpty(Target.Value) Then
' change if column wrong
With Cells(Target.Row, "i")
.Value = Now()
.NumberFormat = "dd mmm yyyy hh:mm:ss"
End With
Else
' change if column wrong
Cells(Target.Row, "i").ClearContents
End If
End If
Application.EnableEvents = True
End Sub

Sandy
 
Absolutely Perfect!!! Thanks.

Rob

Sandy said:
If I read your post you want the date in column "i"? if so here is a
mod otherwise you'll need to change the column letter in the "cells"
statements

Place in the sheet level module


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:H12")) Is Nothing Then
Application.EnableEvents = False
If Not IsEmpty(Target.Value) Then
' change if column wrong
With Cells(Target.Row, "i")
.Value = Now()
.NumberFormat = "dd mmm yyyy hh:mm:ss"
End With
Else
' change if column wrong
Cells(Target.Row, "i").ClearContents
End If
End If
Application.EnableEvents = True
End Sub

Sandy
Click to expand...
 
Back
Top