Home |
Search |
Today's Posts |
#1
|
|||
|
|||
output date modified if data is entered for multiple rows
Hello,
I'm currently creating an excel programin which i want to create some vb code that will check to see if a particular row's (from C to AA) cell has been modified and to then output that modification date to the corresponding row's AB cell. So for instance...if someone modifies the cell H7...then the modification date would be outputted to AB7. I have approximately 100 rows of data that needs this to be done for, and I can get the code to work for an individual row, but I cant figure out how to have it check all the rows in my spreadsheet and output it to the corresponding row. Here is my code for outuputting row 7: Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range Dim KeyRows As Range Dim X As Variant ' The variable KeyCells contains the cells that will ' cause an alert when they are changed. Set KeyCells = Range("D7:AA7") If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then [AB7].Value = "This order was last modified on " & Format(Now, "dd/mm/yy") End If End Sub Any ideas? Thank you so much! Rick |
#2
|
|||
|
|||
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range Dim KeyRows As Range Dim X As Variant Set KeyCells = Range("D7:AA7") If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then Cells(target.row,"AB").Value = "This order was last modified on " & Format(Now,"dd/mm/yy") End If End Sub will add it for whatever row is change. -- HTH Bob Phillips wrote in message ups.com... Hello, I'm currently creating an excel programin which i want to create some vb code that will check to see if a particular row's (from C to AA) cell has been modified and to then output that modification date to the corresponding row's AB cell. So for instance...if someone modifies the cell H7...then the modification date would be outputted to AB7. I have approximately 100 rows of data that needs this to be done for, and I can get the code to work for an individual row, but I cant figure out how to have it check all the rows in my spreadsheet and output it to the corresponding row. Here is my code for outuputting row 7: Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range Dim KeyRows As Range Dim X As Variant ' The variable KeyCells contains the cells that will ' cause an alert when they are changed. Set KeyCells = Range("D7:AA7") If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then [AB7].Value = "This order was last modified on " & Format(Now, "dd/mm/yy") End If End Sub Any ideas? Thank you so much! Rick |
#3
|
|||
|
|||
Thanks for the reply!
I still can only get it to output for row 7 in cell AB7...does it have anything to do with the statement: Set KeyCells = Range("D7:AA7") I have a feeling I need to change that to accomodate all rows, not just row 7. Any ideas? Thanks again! |
#4
|
|||
|
|||
nevermind!...i fixed it...set AA7 to AA100 and it works perfect...thank
you so much!!! |
#5
|
|||
|
|||
Sorry, I forgot to change that bit.
-- HTH Bob Phillips wrote in message oups.com... nevermind!...i fixed it...set AA7 to AA100 and it works perfect...thank you so much!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why won't my newly entered data sort with old data ? | Excel Discussion (Misc queries) | |||
search for latest date | Excel Worksheet Functions | |||
Printing data validation scenarios | Excel Worksheet Functions | |||
Making a auto date entered into a cell permanent from a template | Excel Discussion (Misc queries) | |||
FILL DATES IN VARIOUS CELLS BASED ON A DATE ENTERED IN A SINGLE C. | Excel Discussion (Misc queries) |