"Freezing the time"

  • Hello all,


    I have a blank A-column that is starting from A600;
    next to it a blank B-column that is also starting from B600


    What I need is a code/formula/macro that whenever something will be inserted in column B (starting from B600) -> the column A will react by displaying the current date.
    Furthermore what I really need is that the date will not change once it has been inserted.



    Thanks a lot!

  • Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a value in column B in any row >=600 and exit the cell.

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
        Application.ScreenUpdating = False
        If Target.Row > 599 And Target.Column = 2 Then
            Target.Offset(0, -1) = Date
        End If
        Application.ScreenUpdating = True
    End Sub

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a value in column B in any row >=600 and exit the cell.

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    If Target.Row > 599 And Target.Column = 2 Then
    Target.Offset(0, -1) = Date
    End If
    Application.ScreenUpdating = True
    End Sub


    Oh wow! Thanks you are amazing it works!


    maybe if it is not to much you can help me with another similar issue too. I assumed the code would be the same as in this case however.....
    .
    .
    .


    I have in the same worksheet, column K (again over 600) should return the present static date if the respective row from column I (over 600) displays "TRUE"


    for example


    I603: FALSE K603: *blank*
    I604: TRUE K604: DATE
    I605: FALSE K605: *blank*


    All best,

  • Do you enter "TRUE" manually in column I or is it the result of a formula?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • In the worksheet code module, paste this macro below the previous one. This macro assumes that there is data in cell I599.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • In the worksheet code module, paste this macro below the previous one. This macro assumes that there is data in cell I599.


    it crashes my excel...

  • Can you attach a copy of your file? De-sensitize the data if necessary.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!