Solved: put current date in a column if any change in row

[sreejith]

A newbie to excel and wondering how to do this. I have an excel sheet with say data in column A to F. What I want is if I change the value of any column from A to F, column H should be set to current date automatically.

Any help much appreciated

Sreejith

[GTO]

Greetings Sreejth,

Welcome to vbaexpress!

Are you asking for help with a macro/vba code? Presuming yes for the moment, do you mean that if any of the cells' values in a given row (from columns A - F) are changed by the user (vs. calculation) would result in the date being updated in H(whatever row)?

Mark

[sreejith]

Mark,
Thanks. Yes that's what I am looking for.

I got a piece of code from this site which will update the comment and tried to hack it. But this fills in column H of all the rows. How do I just update the row I changed.

Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Columns("H") = "Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName")
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target = "" Then
preValue = "a blank"
Else: preValue = Target.Value
End If
End Sub

[SamT]

Try this

[vba]
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim H as Long
H = 8 'Column number of "H"
If Target.Count > 1 Then Exit Sub
Cells(Target.Row, H) = Now
End Sub

[/vba]

You'll have to put in some checks to make sure that "Target" is within the range of cells you're watching.

SamT

[lucas]

Adding a few Checks to Sam's code:

[VBA]Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim H As Long
H = 8 'Column number of "H"
If Target.Count > 1 Then Exit Sub 'if more than one cell selected
If Target.Row = 1 Then Exit Sub 'do nothing on header row
If Target.Column >= 1 And Target.Column <= 6 Then ' if in column A -F then add date
Cells(Target.Row, H) = Now
Else
Exit Sub
End If
End Sub
[/VBA]

[sreejith]

Sam/lucas
Thanks very much for your help. Worked perfectly

Thanks
Sreejith

[lucas]

Sreejith, be sure to mark your thread solved using the thread tools at the top of the page.

That way others won't read the entire thread to find that it's been solved.

[sreejith]

Lucas,
Thanks for the reminder.

sreejith

[SamT]

Thank you Lucas.

I had just gotten that little bit tested when SWMBO said, "We're leaving."

Yipes!

To make it a little more self documenting:
[vba]

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Date_Column As Long
Date_Column = 8 'Column number of "H"

Dim Header_Row As Long
Header_Row = 1 'Number of bottom row of headers

If Target.Count > 1 Then Exit Sub 'if more than one cell selected
If Target.Row <= Header_Row Then Exit Sub 'do nothing on header row
If Target.Column >= 1 And Target.Column <= 6 Then ' if in column A -F then add date
Cells(Target.Row, Date_Column) = Now
Else
Exit Sub
End If
End Sub

[/VBA]
SamT

[lucas]

No wonder you were scrambling.

Nice add on the code too. Makes Multiple header rows easy to deal with too.

[SamT]

I always want to be able to come back in a year and understand what's going on at first reading.

SamT