Tracking Change Date

Tom,

Thanks for the information. I used your macro but I receive the below error
when I click in a cell in column "C" to make a change:

Compile error:
Block If without End If

Also, I think your code below had one type-o:
cell.offset(0,1).NumberFormat:="mm/dd/yyyy hh:mm"
I dont beleive there should be a collon before the equal sign. Maybe this is
my problem but I copied and pasted the macro I used for your reference. It is
exactly the same as yours with the one change I mentioned above.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ErrHandler
Set rng = Intersect(Target, Columns(3))
If Not rng Is Nothing Then
Application.EnableEvents = False
For Each cell In rng
'if not isempty (cell) then
cell.Offset(0, 1).Value = Now
cell.Offset(0, 1).NumberFormat = "mm/dd/yyy hh:mm"
cell.EntireColumn.AutoFit
'end if
Next
ErrHandler:
Application.EnableEvents = True
End Sub

Please let me know if you can see any reason why I would be receiving the
error.

Thanks for your help!

Nicole

"Tom Ogilvy" wrote:

> Assuming the change is initiated manually, you can use a macro to do this.
>
> Right click on the worksheet tab and select view code. in the resulting
> module, in the dropdowns at the top of the module, in the left dropdown
> select worksheet and in the right dropdown select Change (not Selection
> Change). You will get an event declaration like:
>
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>
> End Sub
>
> this macro will fire when a cell is edited (whether the value is actually
> changed or not). You can have it update an adjacent cell with the date and
> time
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> On Error goto ErrHandler
> set rng = Intersect(Target,columns(3))
> if not rng is nothing then
> Application.EnableEvents = False
> for each cell in rng
> ' if not isempty(cell) then
> cell.offset(0,1).Value = Now
> cell.offset(0,1).NumberFormat:="mm/dd/yyyy hh:mm"
> cell.EntireColumn.Autofit
> ' end if
> Next
> ErrHandler:
> Application.EnableEvents = True
> End Sub
>
> As written it reacts to changes in column C. Adjust to suit your needs.
>
> If you want to don't want to record a date if the cell is cleared, then
> remove the single quotes in two places.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "TJ" wrote:
>
> > I want to track the date last changed of several cells, so anyone looking at
> > the information can easily see the last updated date. I want the date last
> > changed to show in an adjucant cell. I don't just want to use track changes.
> >
> > --
> > TJ

1. I said don't use selectionchange and then copied it in without noticing.
Anyway, this is tested and works:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHandler
Set rng = Intersect(Target, Columns(3))
If Not rng Is Nothing Then
Application.EnableEvents = False
For Each cell In rng
' if not isempty(cell) then
cell.Offset(0, 1).Value = Now
cell.Offset(0, 1).NumberFormat = "mm/dd/yyyy hh:mm"
cell.Offset(0, 1).EntireColumn.AutoFit
' end if
Next
End If
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


"NikkiB" wrote:

> Tom,
>
> Thanks for the information. I used your macro but I receive the below error
> when I click in a cell in column "C" to make a change:
>
> Compile error:
> Block If without End If
>
> Also, I think your code below had one type-o:
> cell.offset(0,1).NumberFormat:="mm/dd/yyyy hh:mm"
> I dont beleive there should be a collon before the equal sign. Maybe this is
> my problem but I copied and pasted the macro I used for your reference. It is
> exactly the same as yours with the one change I mentioned above.
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> On Error GoTo ErrHandler
> Set rng = Intersect(Target, Columns(3))
> If Not rng Is Nothing Then
> Application.EnableEvents = False
> For Each cell In rng
> 'if not isempty (cell) then
> cell.Offset(0, 1).Value = Now
> cell.Offset(0, 1).NumberFormat = "mm/dd/yyy hh:mm"
> cell.EntireColumn.AutoFit
> 'end if
> Next
> ErrHandler:
> Application.EnableEvents = True
> End Sub
>
> Please let me know if you can see any reason why I would be receiving the
> error.
>
> Thanks for your help!
>
> Nicole
>
> "Tom Ogilvy" wrote:
>
> > Assuming the change is initiated manually, you can use a macro to do this.
> >
> > Right click on the worksheet tab and select view code. in the resulting
> > module, in the dropdowns at the top of the module, in the left dropdown
> > select worksheet and in the right dropdown select Change (not Selection
> > Change). You will get an event declaration like:
> >
> >
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> >
> > End Sub
> >
> > this macro will fire when a cell is edited (whether the value is actually
> > changed or not). You can have it update an adjacent cell with the date and
> > time
> >
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > On Error goto ErrHandler
> > set rng = Intersect(Target,columns(3))
> > if not rng is nothing then
> > Application.EnableEvents = False
> > for each cell in rng
> > ' if not isempty(cell) then
> > cell.offset(0,1).Value = Now
> > cell.offset(0,1).NumberFormat:="mm/dd/yyyy hh:mm"
> > cell.EntireColumn.Autofit
> > ' end if
> > Next
> > ErrHandler:
> > Application.EnableEvents = True
> > End Sub
> >
> > As written it reacts to changes in column C. Adjust to suit your needs.
> >
> > If you want to don't want to record a date if the cell is cleared, then
> > remove the single quotes in two places.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "TJ" wrote:
> >
> > > I want to track the date last changed of several cells, so anyone looking at
> > > the information can easily see the last updated date. I want the date last
> > > changed to show in an adjucant cell. I don't just want to use track changes.
> > >
> > > --
> > > TJ

Thank you very much Tom. This works beautifully! Hope you have a great day!

"Tom Ogilvy" wrote:

> 1. I said don't use selectionchange and then copied it in without noticing.
> Anyway, this is tested and works:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo ErrHandler
> Set rng = Intersect(Target, Columns(3))
> If Not rng Is Nothing Then
> Application.EnableEvents = False
> For Each cell In rng
> ' if not isempty(cell) then
> cell.Offset(0, 1).Value = Now
> cell.Offset(0, 1).NumberFormat = "mm/dd/yyyy hh:mm"
> cell.Offset(0, 1).EntireColumn.AutoFit
> ' end if
> Next
> End If
> ErrHandler:
> Application.EnableEvents = True
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
> "NikkiB" wrote:
>
> > Tom,
> >
> > Thanks for the information. I used your macro but I receive the below error
> > when I click in a cell in column "C" to make a change:
> >
> > Compile error:
> > Block If without End If
> >
> > Also, I think your code below had one type-o:
> > cell.offset(0,1).NumberFormat:="mm/dd/yyyy hh:mm"
> > I dont beleive there should be a collon before the equal sign. Maybe this is
> > my problem but I copied and pasted the macro I used for your reference. It is
> > exactly the same as yours with the one change I mentioned above.
> >
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > On Error GoTo ErrHandler
> > Set rng = Intersect(Target, Columns(3))
> > If Not rng Is Nothing Then
> > Application.EnableEvents = False
> > For Each cell In rng
> > 'if not isempty (cell) then
> > cell.Offset(0, 1).Value = Now
> > cell.Offset(0, 1).NumberFormat = "mm/dd/yyy hh:mm"
> > cell.EntireColumn.AutoFit
> > 'end if
> > Next
> > ErrHandler:
> > Application.EnableEvents = True
> > End Sub
> >
> > Please let me know if you can see any reason why I would be receiving the
> > error.
> >
> > Thanks for your help!
> >
> > Nicole
> >
> > "Tom Ogilvy" wrote:
> >
> > > Assuming the change is initiated manually, you can use a macro to do this.
> > >
> > > Right click on the worksheet tab and select view code. in the resulting
> > > module, in the dropdowns at the top of the module, in the left dropdown
> > > select worksheet and in the right dropdown select Change (not Selection
> > > Change). You will get an event declaration like:
> > >
> > >
> > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > >
> > > End Sub
> > >
> > > this macro will fire when a cell is edited (whether the value is actually
> > > changed or not). You can have it update an adjacent cell with the date and
> > > time
> > >
> > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > On Error goto ErrHandler
> > > set rng = Intersect(Target,columns(3))
> > > if not rng is nothing then
> > > Application.EnableEvents = False
> > > for each cell in rng
> > > ' if not isempty(cell) then
> > > cell.offset(0,1).Value = Now
> > > cell.offset(0,1).NumberFormat:="mm/dd/yyyy hh:mm"
> > > cell.EntireColumn.Autofit
> > > ' end if
> > > Next
> > > ErrHandler:
> > > Application.EnableEvents = True
> > > End Sub
> > >
> > > As written it reacts to changes in column C. Adjust to suit your needs.
> > >
> > > If you want to don't want to record a date if the cell is cleared, then
> > > remove the single quotes in two places.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "TJ" wrote:
> > >
> > > > I want to track the date last changed of several cells, so anyone looking at
> > > > the information can easily see the last updated date. I want the date last
> > > > changed to show in an adjucant cell. I don't just want to use track changes.
> > > >
> > > > --
> > > > TJ