So...
Where does the date go if I change W5?
Where does the date go if I change CB5?
Where does the date go if I change w5:cb3000?

Does the date go in one cell for each row or one cell for each cell that could
be changed?



Bec G (Oz) wrote:

Hi Dave

Thanks for getting back to me. I am still failing at combining the two
statements. The one I have already is this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("w5:CB3146"))
If Not isect Is Nothing Then
Target.Interior.ColorIndex = 3
End If
End Sub

The person I am building the model for originally said all they wanted was
the cell colour to change if the data changed. Now they have also requested a
column called Adjustment Date and I need to not only show the cell colour
change in the data range but also need to put the date in the column which is
a couple of columns to the right of the range.

Can you help?

Bec G

"Dave Peterson" wrote:

Each sheet's module can have at most one worksheet_Change event.

If you want to monitor changes to different areas in that worksheet, you'll have
to make the code in the single worksheet_change event handle it.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then
Exit Sub 'single cell at a time
End If

If Not (Intersect(Target, Me.Range("A1")) Is Nothing) Then
'A1 changed
MsgBox "A1 changed!" 'do the stuff for this range
ElseIf Not (Intersect(Target, Me.Range("c1")) Is Nothing) Then
MsgBox "C1 Changed!" 'do the stuff for this range
End If

End Sub

Bec G (Oz) wrote:

Hi Bernie

I've been looking for formula to do exactly this and have tried it but I
have another formula in the same sheet that starts with

Private Sub Worksheet_Change(ByVal Target As Range)

so I am getting an "Ambiguous name" error.

I tried to change the first line fo the formula to

Private Sub Worksheet_Change2(ByVal Target As Range) or
Private Sub Worksheet_dateChange(ByVal Target As Range)

But the macro will then not work.

Can you advise how I can fix this?

Thanks

Bec G

"Bernie Deitrick" wrote:

j,

The following will put the record on the same row, but 4 columns over,
recording any changes to the block of cells A1:D100. (So the records are
written in E1:H100)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Not Intersect(Target, Range("A1:D100")) Is Nothing Then
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("A1:D100"))
myCell.Offset(0, 4).Value = "Cell " & _
myCell.Address(False, False) & " was changed " & _
Format(Now(), "mmm dd, yyyy at hh:mm:ss")
Next myCell
Application.EnableEvents = True
End If
End Sub

HTH,
Bernie
MS Excel MVP

"jandersen" wrote in message
...
This works if I'm making changes in one cell and want the reply to appear
in
another cell...however, I can't make it work for changes made to a range
of
cells and the reply to show in the another range of cells (much like
dragging
a function down a column). I think it's just my syntax in modifying the
code
you gave me...please help!

"Bernie Deitrick" wrote:

j,

Copy the code below, right click on the sheet tab and select "View Code"
and
paste the code into the window that appears.

Change the range addresses to correspond to the cells you want to
monitor /
use to record.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.EnableEvents = False
Range("A2").Value = "Cell A1 was changed " & _
Format(Now(), "mmm dd, yyyy at hh:mm:ss")
Application.EnableEvents = True
End If
End Sub

"jandersen" wrote in message
...
I want to insert a function into a cell that displays the date another
cell
was modified. How can I do this?







--

Dave Peterson


--

Dave Peterson