Date Last Modified (in cell)

[Jarmadon]

I want to know if there is a way to have a cell beside a series of cells that will change its date (and time) when any of the cells in the series is changed.

On a single row, I have four materials that total to a fifth cell. The 6th cell in that row, I would like to have a "date last modified" that would change if any of the first five cell's value is changed.

Is this possible?

 

[lenze]

Hi and welcome to the Board!!
In the WorkSheet module

Private Sub Worksheet_Change(byVal Target as Range)
If Target.Count > 1 Then exit Sub
If Target.Row < 2 Then Exit Sub
If Target.Column > 4 Then Exit Sub
Cells(Target.Row, 6) = Now
End Sub

lenze

 

[Jarmadon]

I am a novice at Excel and do not recognize what you have written. Is there a function or such? Or is this the only way?

thanks!

 

[lenze]

There is no Function tht I am aware of that would do this. This is the only way I know of that you can do that for each change in your row. It is called an Event Procedure. RightClick the Sheet Tab and choose "View Code'. Copy and paste the code above to the white panel. Return to Excel(Alt+Q). Now, as written, whenever a cell in changed in Columns A:D, Rows 2 or down, the date and time will be entered in Column "F" in the same row as the changed cell. If those ranges don't fit your sheet, post back where the cells you want to monitor are and where you want the Date/Time. If you want, you could also place the name of the user who made the change. You can even add a comment to the changed cell with the time, who changed it ,and the previous value. If any of that has interest, post back.

lenze

 

[Jarmadon]

Thanks!!

Here are the details of my worksheet ....

The first row is row #6.
The columns are columns D-K
The column for the "Date Last Modified" answer is column M
The last row is currently row #9040, but may grow.

Couple of questions:
1. If I insert a column between D and K, what does that do?
2. If I insert a row between #6 and #9040, what does that do?

Thanks Again!!

 

[lenze]

This is very doable. The only problem will be inserting COLUMNS. That will require some tricky programing. How likely is that to occur? Also, is Column "L" Blank? Inserting a Row will not matter.
lenze

 

[Jarmadon]

I do not really see a need to insert a column after this is set up. So I think I am fine there. We will be inserting a row, but as you said, that should not be a problem.

Column L is being used for something totally different, and I would rather keep it there if possible. But I can move it to the other side of Column M if I need to.

Thanks!

 

[lenze]

OK, lets assume you may want to add or delete a Column in the future. With a little prep work, we can allow for that. Given your example, select Cell K1. Choose Insert>Name>Define. For name, enter myCol. Click OK. Now insert this code in the WorkSheet module. RightClick the sheet tab and choose "View Code"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Row < 6 Then Exit Sub
Dim myCol As Integer
myCol = Range("myCol").Column
If Target.Column > myCol Or Target.Column < 4 Then Exit Sub
Cells(Target.Row, myCol + 2) = Now
End Sub

Should work and allow you to insert/delete columns

lenze

 

[Jarmadon]

Sorry to be such a pain, but I went to Cell K1, right clicked and choose "Insert", but did not get an option to "Name". Just two options to shift the cells and two options to insert an entire row or column.

I do have an option to Name Range, is that what you are talking about?

I am running Excel 2007, if that makes a difference.

Thank you for your patience!

 

[lenze]

OK, That's what yu want. It's different in 2007. So, with K1 selected, choose "Name Range" and follow the dialog.

lenze