Cell range last modified formula

[k0pimi]

First post, long time excel user, becoming a power user in light of this project.


The situation:

I have a pricing spreadsheet that has all the pricing components listed in a row, which involve calculations, formula's, and text data. The sheet is 1000 rows of different products and prices.

The need:

I need to be able to have the last cell of the row to have a "Date last modified" in it. I have already tried VBA to give me the date of a single cell was last modified, but how could I timestamp when the last time the ROW was modified? It wouldn't be modified directly either, some of the cells use INDEX,MATCH,MATCH functions to other sheets.

The example:


I have a price that includes inbound freight to a hub, interbranch freight to a servicing branch, header freight to the customer, and extra charges added to the price. The individual freights are listed on tables on other sheets, and a function pulls the required one into the main price table. Any time a freight is changed on the freight table, the original price row needs to be updated with a new date.

I know that I could use basic VBA to get the date that each indiviual freight changes, and have them listed in 3 different columns on the main page (which could be useful, but not desired), but what about if anything on the row changes? It doesn't matter if it is just the freight component or not.




Any VBA masters out there have an answer?

 

[AlphaFrog]

One idea:

• When you open the workbook, read all your Pricing data in an array or copy just the values to a temporary sheet.
• When you close the workbook, VBA compares the current pricing data to the stored data row-by-row and time-stamps the changed rows and saves.

 

[k0pimi]

One idea:

• When you open the workbook, read all your Pricing data in an array or copy just the values to a temporary sheet.
• When you close the workbook, VBA compares the current pricing data to the stored data row-by-row and time-stamps the changed rows and saves.

I know that will work, but I am developing this pricing spreadsheet for an analyst that will not want to have to do that everytime he uses the spreadsheet (everyday).

Any other automated ideas?

 

[AlphaFrog]

This will date stamp changed rows.
Put this in the Pricing sheet's code module
Change the DateStampColumn number to suit

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]Dim[/COLOR] r [COLOR=darkblue]As[/COLOR] Range, c [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Const[/COLOR] DateStampColumn [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR] = 10    [COLOR=green]'Date stamp column number[/COLOR]
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] r [COLOR=darkblue]In[/COLOR] Target.Rows
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] c [COLOR=darkblue]In[/COLOR] r.Cells
            [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] IsEmpty(c) [COLOR=darkblue]Then[/COLOR]
                Application.EnableEvents = [COLOR=darkblue]False[/COLOR]
                Cells(r.Row, DateStampColumn).Value = Date
                Application.EnableEvents = [COLOR=darkblue]True[/COLOR]
                [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]For[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Next[/COLOR] c, r
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

This works automatically for manually changed and pasted values. It doesn't work with changes from formulas. You did say you could get the dates of freight changes. Additional code could be added to the Workbook_Save event procedure to update the date stamp column if it's less than the freight dates. Alternatively, you could add code to the Freight Rate worksheet's change procedure to update the row date-stamps on the Pricing sheet. More details on your data configuration would be needed if you want help with that.

 

[paramita]

Dear AlphaFrog, your post solved my issue and I registered purely to send you a warm thank you!
Generous souls like you make the world a better place!

 

[AlphaFrog]

Dear AlphaFrog, your post solved my issue and I registered purely to send you a warm thank you!
Generous souls like you make the world a better place!

You're welcome and I appreciate the feedback. That's more than k0pimi was willing to do.

 

[Milu]

Hi Team,

This is very usefull code. I like this. But i want few changes in this. Can you please help to do this.
I want to do is, If i modify cell in only A Collumn then i want to update modifyed date in collumn 10.
Can we do this? I have try below code but it is not work.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Const DateStampColumn As Long = 10 'Date stamp column number
For Each c In Target.Range("a:a")
If Not IsEmpty(c) Then
Application.EnableEvents = False
Cells(r.Row, DateStampColumn).Value = Format(Now, "DD-MMM-YY HH:MM:SS:SSS")
Application.EnableEvents = True
End If
Next
End Sub

 

[AlphaFrog]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    [color=darkblue]Dim[/color] c [color=darkblue]As[/color] Range
    [color=darkblue]If[/color] Intersect(Range("A:A"), Target) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]For[/color] [color=darkblue]Each[/color] c [color=darkblue]In[/color] Intersect(Range("A:A"), Target)
        [color=darkblue]If[/color] [color=darkblue]Not[/color] IsEmpty(c) [color=darkblue]Then[/color]
            Application.EnableEvents = [color=darkblue]False[/color]
                Cells(c.Row, "J").Value = Format(Now, "DD-MMM-YY HH:MM:SS:SSS")
            Application.EnableEvents = [color=darkblue]True[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] c
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[mdalby]

AlphaFrog, this VBS has been so helpful for me. You made my life easier. One problem. I have some header rows for the top 8 rows in my spreadsheet. I occassional inadvertantly make a typo in these header rows and it inserts the modified date in the header.

What can I add to exclude the header rows from this VBS script?

Thanks for the help.

 

[AlphaFrog]

AlphaFrog, this VBS has been so helpful for me. You made my life easier. One problem. I have some header rows for the top 8 rows in my spreadsheet. I occassional inadvertantly make a typo in these header rows and it inserts the modified date in the header.

What can I add to exclude the header rows from this VBS script?

Thanks for the help.

I'm not sure what version of the code you're using.

This excludes the top 8 rows using the original version (post #4)

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    [color=darkblue]Dim[/color] r [color=darkblue]As[/color] Range, c [color=darkblue]As[/color] Range
    [color=darkblue]Const[/color] DateStampColumn [color=darkblue]As[/color] [color=darkblue]Long[/color] = 10    [color=green]'Date stamp column number[/color]
    [color=darkblue]For[/color] [color=darkblue]Each[/color] r [color=darkblue]In[/color] Target.Rows
        [color=darkblue]If[/color] r.Row > 8 [color=darkblue]Then[/color]
            [color=darkblue]For[/color] [color=darkblue]Each[/color] c [color=darkblue]In[/color] r.Cells
                [color=darkblue]If[/color] [color=darkblue]Not[/color] IsEmpty(c) [color=darkblue]Then[/color]
                    Application.EnableEvents = [color=darkblue]False[/color]
                    Cells(r.Row, DateStampColumn).Value = Date
                    Application.EnableEvents = [color=darkblue]True[/color]
                    [color=darkblue]Exit[/color] [color=darkblue]For[/color]
                [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]Next[/color] c
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] r
[color=darkblue]End[/color] [color=darkblue]Sub[/color]