ExcelBanter - Last time modified timestamp

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Last time modified timestamp (https://www.excelbanter.com/excel-programming/339137-last-time-modified-timestamp.html)

Last time modified timestamp

I have code that enters date and time in column E if 100 % (1) is entered in
column D, how do i modify this code to have 1 more timestamp in column F
which would be entered each time i change value in column B?

What i have now is - "task complete timestamp", now i want to add "last time
modified timestamp"

This is the code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Intersect(.Cells, Me.Range("D:D")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
Application.EnableEvents = False
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

Last time modified timestamp

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errHandler:
Application.EnableEvents = False

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Not Intersect(.Cells, Me.Range("D:D")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
ElseIf Not Intersect(.Cells, Me.Range("B:B")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 4)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Mikus" wrote in message
...
I have code that enters date and time in column E if 100 % (1) is entered
in
column D, how do i modify this code to have 1 more timestamp in column F
which would be entered each time i change value in column B?

What i have now is - "task complete timestamp", now i want to add "last
time
modified timestamp"

This is the code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Intersect(.Cells, Me.Range("D:D")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
Application.EnableEvents = False
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

Last time modified timestamp

Yes but i wanted last modified column to respond to ANY changes in column B
not only if i enter 1 (100%) but if i enter any value

"Bob Phillips" wrote:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errHandler:
Application.EnableEvents = False

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Not Intersect(.Cells, Me.Range("D:D")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
ElseIf Not Intersect(.Cells, Me.Range("B:B")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 4)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Mikus" wrote in message
...
I have code that enters date and time in column E if 100 % (1) is entered
in
column D, how do i modify this code to have 1 more timestamp in column F
which would be entered each time i change value in column B?

What i have now is - "task complete timestamp", now i want to add "last
time
modified timestamp"

This is the code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Intersect(.Cells, Me.Range("D:D")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
Application.EnableEvents = False
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

Last time modified timestamp

So remove that test

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errHandler:
Application.EnableEvents = False

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Not Intersect(.Cells, Me.Range("D:D")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
ElseIf Not Intersect(.Cells, Me.Range("B:B")) Is Nothing Then
If IsNumeric(.Value) Then
With .Offset(0, 4)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Mikus" wrote in message
...
Yes but i wanted last modified column to respond to ANY changes in column
B
not only if i enter 1 (100%) but if i enter any value

"Bob Phillips" wrote:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errHandler:
Application.EnableEvents = False

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Not Intersect(.Cells, Me.Range("D:D")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
ElseIf Not Intersect(.Cells, Me.Range("B:B")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 4)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Mikus" wrote in message
...
I have code that enters date and time in column E if 100 % (1) is
entered
in
column D, how do i modify this code to have 1 more timestamp in column
F
which would be entered each time i change value in column B?

What i have now is - "task complete timestamp", now i want to add
"last
time
modified timestamp"

This is the code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Intersect(.Cells, Me.Range("D:D")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
Application.EnableEvents = False
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

Last time modified timestamp

I get following error when this macro triggers:

Compile error
End With without With

"Bob Phillips" wrote:

So remove that test

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errHandler:
Application.EnableEvents = False

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Not Intersect(.Cells, Me.Range("D:D")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
ElseIf Not Intersect(.Cells, Me.Range("B:B")) Is Nothing Then
If IsNumeric(.Value) Then
With .Offset(0, 4)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Mikus" wrote in message
...
Yes but i wanted last modified column to respond to ANY changes in column
B
not only if i enter 1 (100%) but if i enter any value

"Bob Phillips" wrote:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errHandler:
Application.EnableEvents = False

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Not Intersect(.Cells, Me.Range("D:D")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
ElseIf Not Intersect(.Cells, Me.Range("B:B")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 4)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Mikus" wrote in message
...
I have code that enters date and time in column E if 100 % (1) is
entered
in
column D, how do i modify this code to have 1 more timestamp in column
F
which would be entered each time i change value in column B?

What i have now is - "task complete timestamp", now i want to add
"last
time
modified timestamp"

This is the code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Intersect(.Cells, Me.Range("D:D")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
Application.EnableEvents = False
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

Last time modified timestamp

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errHandler:
Application.EnableEvents = False

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Not Intersect(.Cells, Me.Range("D:D")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
ElseIf Not Intersect(.Cells, Me.Range("B:B")) Is Nothing Then
If IsNumeric(.Value) Then
With .Offset(0, 4)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If 'added line
End With

errHandler:
Application.EnableEvents = True

End Sub

Regards
Rowan

Mikus wrote:
I get following error when this macro triggers:

Compile error
End With without With

"Bob Phillips" wrote:

So remove that test

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errHandler:
Application.EnableEvents = False

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Not Intersect(.Cells, Me.Range("D:D")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
ElseIf Not Intersect(.Cells, Me.Range("B:B")) Is Nothing Then
If IsNumeric(.Value) Then
With .Offset(0, 4)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Mikus" wrote in message
...

Yes but i wanted last modified column to respond to ANY changes in column

B

not only if i enter 1 (100%) but if i enter any value

"Bob Phillips" wrote:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errHandler:
Application.EnableEvents = False

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Not Intersect(.Cells, Me.Range("D:D")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
ElseIf Not Intersect(.Cells, Me.Range("B:B")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 4)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Mikus" wrote in message
...

I have code that enters date and time in column E if 100 % (1) is

entered

in

column D, how do i modify this code to have 1 more timestamp in column

F

which would be entered each time i change value in column B?

What i have now is - "task complete timestamp", now i want to add

"last

time

modified timestamp"

This is the code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Intersect(.Cells, Me.Range("D:D")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
Application.EnableEvents = False
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub