Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 13th 05, 04:25 PM
 
Posts: n/a
Default 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   Report Post  
Old October 13th 05, 04:34 PM
Bob Phillips
 
Posts: n/a
Default

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   Report Post  
Old October 13th 05, 05:03 PM
 
Posts: n/a
Default

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   Report Post  
Old October 13th 05, 05:06 PM
 
Posts: n/a
Default

nevermind!...i fixed it...set AA7 to AA100 and it works perfect...thank
you so much!!!

  #5   Report Post  
Old October 13th 05, 08:09 PM
Bob Phillips
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Why won't my newly entered data sort with old data ? laura Excel Discussion (Misc queries) 2 August 3rd 05 04:26 PM
search for latest date Sue Excel Worksheet Functions 2 August 2nd 05 11:09 PM
Printing data validation scenarios SJC Excel Worksheet Functions 14 July 24th 05 12:43 AM
Making a auto date entered into a cell permanent from a template Muncher Excel Discussion (Misc queries) 2 May 26th 05 11:07 PM
FILL DATES IN VARIOUS CELLS BASED ON A DATE ENTERED IN A SINGLE C. dencrowell Excel Discussion (Misc queries) 1 April 14th 05 04:11 AM


All times are GMT +1. The time now is 06:49 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017