I would like to have the date show in row'11' if I modify anything in
row '12'. I want to apply this code to the entire row of '11' so that
if I modify 'c12', 'c11' will show the date and so on.


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=402018

One way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRngToCheck As Range
Dim myCell As Range

Set myRngToCheck = Me.Rows(12)

If Intersect(Target, myRngToCheck) Is Nothing Then Exit Sub

On Error Resume Next 'try to get all the cells
Application.EnableEvents = False
For Each myCell In Intersect(Target, myRngToCheck).Cells
With myCell.Offset(-1, 0)
.Value = Date
.NumberFormat = "mm/dd/yyyy"
'or maybe?
' .Value = Now
' .NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
Next myCell
Application.EnableEvents = True
On Error GoTo 0

End Sub

Rightclick on the worksheet tab that should have this behavior. Select view
code and paste this into the code window.

Then back to excel and test it out.

"oberon.black" wrote:

I would like to have the date show in row'11' if I modify anything in
row '12'. I want to apply this code to the entire row of '11' so that
if I modify 'c12', 'c11' will show the date and so on.

--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=402018

--

Dave Peterson

Hi not sure if this is what you want but paste this into the code pag
for the sheet concerned, then when you click any cell in row 12 th
date and time will appear in row 11 as long as row 11 is formatted a
date!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Row = 12 Then
ActiveCell.Offset(-1, 0) = Date
End If

End Sub

HTH

Simo

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=40201

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
If Target.Count = 1 And Target.Row = 12 Then
Cells(11, Target.Column) = Date
End If
ErrorHandler:
Application.EnableEvents = True
End Sub

This is worksheet event code. Right click the sheet tab, select View Code
and paste the code in there.

Hope this helps
Rowan

"oberon.black" wrote:


I would like to have the date show in row'11' if I modify anything in
row '12'. I want to apply this code to the entire row of '11' so that
if I modify 'c12', 'c11' will show the date and so on.


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=402018

These codes all work perfectly.

Than

--
oberon.blac
-----------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...fo&userid=2673
View this thread: http://www.excelforum.com/showthread.php?threadid=40201