Hi Dave,

This keeps debugging on the myIntersect bit as the variable is coming
through as nothing in the locals window and then its exiting the sub (as that
is what it's supposed to do).

Any reason why this would happen?

"Dave Peterson" wrote:

This will put the date/time in AK whenever AJ changes to Yes:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngToInspect As Range
Dim myIntersect As Range
Dim myCell As Range

Set RngToInspect = Me.Range("AJ13:AJ2000")
Set myIntersect = Intersect(Target, RngToInspect)

If myIntersect Is Nothing Then
Exit Sub
End If

For Each myCell In myIntersect.Cells
If LCase(myCell.Value) = LCase("yes") Then
Application.EnableEvents = False
With Me.Cells(myCell.Row, "AK")
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
Next myCell
End Sub


It's a worksheet event. Rightclick on the worksheet tab that should have this
behavior and select view code.

Then paste this into newly opened code window.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

N1KO wrote:

I'm after a macro that'll place the date & time into a cell when another cell
is changed from No to Yes.

I need the date to then be fixed (was thinking using a Now() function but
it'd change on every re-calculate) until the cell is changed again.

The Yes & No will be cells 13-2000 in column AJ & date/time in cells 13-2000
in column AK.

Naturally the cells in each column will relate (AJ13 changes then AK13 will
display the date, etc).

Thanks

--

Dave Peterson