Results 1 to 7 of 7

Thread: A new challenge

  1. #1
    Join Date
    Feb 2004
    Location
    USA - California
    Posts
    21

    Talking Unanswered: A new challenge

    How can I use an "if" statement or some other way to calculate a formula when a cell value changes? Something like: =if(A1 changes value,(today), no change) This formula would reside in cell C1 and would display todays date in cell C1 if the value in A1 has changed today. If it has not changed today, it would leave the previous date (whatever it would happen to be from the last time the value in A1 has changed) in the cell. The value in A1 would be changed by typing the value directly in A1. This sounded simple.....but now I'm can't find a function to detect the change in value.....any help out there please?

    Thank you.....Melinda

  2. #2
    Join Date
    Feb 2004
    Location
    USA - California
    Posts
    21

    I lost the reply.....

    Something happenned....I wanted to reply to the person who replied to me, and somehow I lost the post. I am new to VBA and I can't get the code to work. The cells that are changing value are in column G. The current date is in column K. I want the date in column K to change as the value in column G changes.....or put the new date in column L. The rows are 3 through 2950. How do I use the code with that range?
    Thank you,
    Melinda

  3. #3
    Join Date
    Feb 2004
    Location
    Canada
    Posts
    133
    Sorry about that, I discovered my mistake and thought I could quietly delete the post before someone read it! Try this code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 7 And (Target.Row > 2 And Target.Row < 2351) Then
    Cells(Target.Row, 11) = Now
    End If
    End Sub

    That should do the trick.

  4. #4
    Join Date
    Feb 2004
    Location
    USA - California
    Posts
    21

    almost there.....

    Originally posted by bmacr [/i]
    Sorry about that, I discovered my mistake and thought I could quietly delete the post before someone read it! Try this code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 7 And (Target.Row > 2 And Target.Row < 2351) Then
    Cells(Target.Row, 11) = Now
    End If
    End Sub

    That should do the trick.


    No problem....thought I just messed things up. That works execpt it puts the date in one cell only. I want it to put the date in the corresponding row with the value change. If the values changed on row 1100, I want the date to appear in row 1100.

    Thank you!!!

  5. #5
    Join Date
    Feb 2004
    Location
    Canada
    Posts
    133
    Hmm... on my machine if I change a value in G20, today's date appears in K20. What cell does it appear in on yours?

  6. #6
    Join Date
    Feb 2004
    Location
    USA - California
    Posts
    21
    got it now...had a typo. I put column instead of row in that last bit. Thank you so much!!!!

  7. #7
    Join Date
    Feb 2004
    Location
    Canada
    Posts
    133
    My pleasure. Glad you got it working.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •