View Full Version : Solved: put current date in a column if any change in row
sreejith
02-19-2010, 03:42 PM
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
Greetings Sreejth,
Welcome to vbaexpress! :thumb
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
02-19-2010, 04:02 PM
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
Try this
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
You'll have to put in some checks to make sure that "Target" is within the range of cells you're watching.
SamT
lucas
02-20-2010, 08:17 AM
Adding a few Checks to Sam's code:
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
sreejith
02-20-2010, 11:50 AM
Sam/lucas
Thanks very much for your help. Worked perfectly
Thanks
Sreejith
lucas
02-20-2010, 11:57 AM
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
02-20-2010, 02:26 PM
Lucas,
Thanks for the reminder.
sreejith
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:
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
SamT
lucas
02-21-2010, 08:35 AM
No wonder you were scrambling.
Nice add on the code too. Makes Multiple header rows easy to deal with too.
I always want to be able to come back in a year and understand what's going on at first reading.
SamT
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.