Excel expert required!

  Jonathan314159 14:45 22 Mar 2007

I have a simple spreadsheet which requires responses from a variety of people. There is a column containing issues with a column to the right where they are meant to put columns stating what they are doing about the issue - and which is of course meant to be updated regularly.

Is there any way that I can tell when a particular comment was updated - ie I would like a third column which automatically recorded the date when they update the comment on a particular issue.

  VoG II 14:51 22 Mar 2007

Right click the sheet tab > View Code. Copy and paste in the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TargetCol As Integer
TargetCol = 4 'assumes comment is added in column D - change to suit
If Target.Count > 1 Then Exit Sub
If Target.Column <> TargetCol Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, 1).Value = Date
Application.EnableEvents = True
End Sub

Close the code window and try entering something in column D. You may have to increase the width of column E to see the result.

  silverous 15:04 22 Mar 2007

VoG definitely gets emailed whenever the word excel shows up in a post :)

  Jonathan314159 17:30 22 Mar 2007

VoG I shall try that, many thanks

  Jonathan314159 17:32 22 Mar 2007

It works.

Stunning, I thought I was going to get a "can't be done" response.

Brilliant thank you again!

  postie24 19:06 22 Mar 2007

VoG™ to the rescue yet again

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

What went wrong at the Designs of the Year 2017

iPhone X news: Release date, price, new features & specs

Comment regarder des séries et talk-shows américains en France ?