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?

 

[mdalby]

That did the trick. Thank you

 

[mdalby]

That worked great. Thank you!!!

 

[Cushion16]

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]

I was wondering if you could help me with this. This formula has been the only formula I have gotten to work. I modified the formula to cover an entire row like this:

Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range If Intersect(Range("A2:A15"), Target) Is Nothing Then Exit Sub For Each c In Intersect(Range("A2:A15"), Target) If Not IsEmpty(c) Then Application.EnableEvents = False Cells(c.Row, "J").Value = Format(Now, "DD-MMM-YY") Application.EnableEvents = True End If Next c End Sub</pre>But my sheet is 100+ rows and 15+columns so is there a way I can apply this formula to all the rows in the worksheet independently without having to enter this formula over for every row?

 

[AlphaFrog]

I modified the formula to cover an entire row like this:

But my sheet is 100+ rows and 15+columns so is there a way I can apply this formula to all the rows in the worksheet independently without having to enter this formula over for every row?

Hi and welcome to the forum.

Can you better describe your data configuration and what you are trying to do?

 

[Cushion16]

Hi and welcome to the forum.

Can you better describe your data configuration and what you are trying to do?

I want the date modified to be auto-populated for every row. I have over 100 rows and 15 columns I will be applying this to. How can I do this using your formula?

 

[Cushion16]

Any update? I cannot find this information anywhere else. Please help

 

[Hawks18]

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.

Alpha Frog,

Is there a way to make this work with changes from formulas? I'm trying to do something very similar to this, but I'd like to detect changes made to a cell from some formulaic calculations...

 

[AlphaFrog]

Not easily, but depending on the formulas and your data configuration, there may be ways around it.

Your private message box is full.

 

[Hawks18]

Not easily, but depending on the formulas and your data configuration, there may be ways around it.

Your private message box is full.

AlphaFrog,

Thank you for the quick response! I'm new to MrExcel and I don't have any e-mail yet, but I'll try to figure out why it's "full" right now...

As for my inquiry, one of your above listed solutions seemed to work for my data configuration, but it was monitoring an entire row rather than a specific cell and I couldn't decipher the syntax for a specific row or range.

I don't have the file with me right now, though. I can provide you with specifics tomorrow...hopefully , I can figure out the e-mail situation, too!