Last time modified timestamp

[Mikus]

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

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Mikus]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

[Mikus]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>

[Rowan]

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" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>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" <[email protected]> wrote in message
>>>>news:[email protected]...
>>>>
>>>>>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
>>>>
>>>>
>>>>
>>
>>