Note: The other languages of the website are Google-translated. Back to English
Se connecter  \/ 
x
or
x
S'inscrire  \/ 
x

or

Comment enregistrer automatiquement la date et l'heure lorsque la cellule change?

Il est facile pour nous d'insérer manuellement une date et une heure statiques ou d'insérer une date dynamique changeant avec l'heure système avec une formule. Si vous souhaitez enregistrer automatiquement la date et l'heure lorsque vous modifiez ou entrez des valeurs, ce problème peut être quelque peu différent à traiter. Mais, dans cet article, vous pouvez résoudre cette tâche avec les étapes suivantes.

Enregistrez automatiquement la date et l'heure lorsque la cellule change avec le code VBA

Onglet Office Activez la modification et la navigation par onglets dans Office et simplifiez considérablement votre travail ...
Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%
  • Réutiliser n'importe quoi: Ajoutez les formules, les graphiques et tout autre élément les plus utilisés ou les plus complexes à vos favoris et réutilisez-les rapidement à l'avenir.
  • Plus de 20 fonctionnalités de texte: Extraire le numéro de la chaîne de texte; Extraire ou supprimer une partie des textes; Convertissez les nombres et les devises en mots anglais.
  • Outils de fusion: Plusieurs classeurs et feuilles en un seul; Fusionner plusieurs cellules / lignes / colonnes sans perdre de données; Fusionner les lignes en double et la somme.
  • Outils de fractionnement: Diviser les données en plusieurs feuilles en fonction de la valeur; Un classeur vers plusieurs fichiers Excel, PDF ou CSV; Une colonne à plusieurs colonnes.
  • Coller sauter Lignes cachées / filtrées; Compte et somme par couleur d'arrière-plan; Envoyez des e-mails personnalisés à plusieurs destinataires en masse.
  • Super filtre: Créez des schémas de filtres avancés et appliquez-les à toutes les feuilles; Trier par semaine, jour, fréquence et plus encore; Filtre par gras, formules, commentaire ...
  • Plus de 300 fonctionnalités puissantes; Fonctionne avec Office 2007-2019 et 365; Prend en charge toutes les langues; Déploiement facile dans votre entreprise ou organisation.

flèche bleue bulle droite Enregistrez automatiquement la date et l'heure lorsque la cellule change avec le code VBA


Par exemple, j'ai une plage de valeurs, et maintenant, lorsque je change ou saisis de nouvelles valeurs dans la colonne B, je veux qu'il enregistre automatiquement la date et l'heure actuelles dans la colonne C comme la capture d'écran suivante:

doc-update-time-value-changes-1

Vous pouvez terminer cette tâche avec le code VBA suivant. Veuillez faire comme ceci:

1. Maintenez le ALT + F11 clés pour ouvrir le Fenêtre Microsoft Visual Basic pour Applications.

2. Ensuite, choisissez votre feuille de calcul utilisée à gauche Explorateur de projets, double-cliquez dessus pour ouvrir le Module, puis copiez et collez le code VBA suivant dans le module vide:

Code VBA: enregistrez automatiquement la date et l'heure lorsque la cellule change

Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140722
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Now
            Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

doc-update-time-value-changes-1

3. Ensuite, enregistrez et fermez ce code pour revenir à la feuille de calcul, maintenant lorsque vous modifiez la valeur de la cellule ou tapez de nouvelles données dans la colonne B, la date et l'heure seront automatiquement enregistrées dans la colonne C.

Notes:

1. Dans le code ci-dessus, vous pouvez modifier le "B: B»Vers toute autre colonne dont vous souhaitez modifier les valeurs de cellule dans ce script: Définissez WorkRng = Intersect (Application.ActiveSheet.Range ("B: B"), Target).

2. Avec cela xColonne de décalage = 1 script, vous pouvez insérer et mettre à jour la date et l'heure dans la première colonne à côté de votre colonne de valeur changeante, vous pouvez changer le numéro 1 en d'autres nombres, tels que 2,3,4,5… cela signifie que la date sera insérée le deuxième, troisième, quatrième ou cinquième colonne en plus de la colonne des valeurs modifiées.

3. Lorsque vous supprimez une valeur dans la colonne modifiée, la date et l'heure seront également supprimées.


Les meilleurs outils de productivité de bureau

Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
  • Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtrée...
  • Fusionner les cellules / lignes / colonnes sans perdre de données; Contenu des cellules divisées; Combiner des lignes / colonnes en double... Empêcher les cellules en double; Comparer les gammes...
  • Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellules...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
  • Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus de 300 fonctionnalités puissantes. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Essai gratuit de 30 jours. Garantie de remboursement de 60 jours.
onglet kte 201905

Office Tab apporte une interface à onglets à Office et simplifie considérablement votre travail

  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
bas de cabine
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Ahtisham Haider · 9 days ago
    Sir ! I have a problem that I have data entering Column B in sheet 1 and Date Column C in sheet 2....please guide me what will be the code for that...Anyone
  • To post as a guest, your comment is unpublished.
    Fiona · 1 months ago
    I want to use this code to update multiple cells within the same sheet. How do I write this?
    For example, 
    when cell C12 changes, C13 gets the date
    when cell C26 changes, C27 gets the date
    when cell G13 changes, G14 gets the date
    and so on
  • To post as a guest, your comment is unpublished.
    justme · 2 months ago
    datetime    auto_cycle    tool    time_difference
    01-01-2021 :10:10:09    1    1    00:00:11
    03-01-2021 :10:10:20    1    2    00:00:02
    13-10-2021 :10:10:22    1    3    00:00:04
    13-10-2021 :10:10:26    1    4    00:00:04
    13-10-2021 :10:10:30    1    5    00:00:06
    13-10-2021 :10:10:36    1    6    00:00:02
    13-10-2021 :10:10:38    1    7    00:00:05
    13-10-2021 :10:10:43    1    8    00:00:00
    13-10-2021 :10:10:43    1    9    00:00:06
    13-10-2021 :10:10:49    1    10    00:00:03
    13-10-2021 :10:10:52    1    11    00:00:08
    13-10-2021 :10:11:00    1    13    00:00:10
    13-10-2021 :10:11:10    1    12    00:00:04
    13-10-2021 :10:11:14    1    14    00:00:05
    13-10-2021 :10:11:19    1    16    00:00:04
    13-10-2021 :10:11:23    1    17    00:00:04
    13-10-2021 :10:11:27    1    18    00:00:02
    13-10-2021 :10:11:29    1    19    00:00:04
    13-10-2021 :10:11:33    1    20    00:00:05
    13-10-2021 :10:11:38    1    21    00:00:07
    13-10-2021 :10:11:45    1    12    

    this is my master file suppose i change the time value in a column corresponding time difference sshould be update in d column automatically without trigger the macro assigning button could you please help me out 
  • To post as a guest, your comment is unpublished.
    JustMe · 9 months ago
    I see a couple of people asking about formulas and I'm sorry if I missed the answer somewhere in the thread.
    I am updating very large data sets that then are migrated to hidden sheets.
    I have those sheets each set with this "latest update" code but since everything on those sheets references back to the larger data set, it does not see a cell update as the formulas remain the same even thought the values change.
    I need it to update when the value changes and not jus when the content of the cell changes.
    • To post as a guest, your comment is unpublished.
      Ahtisham Haider · 9 days ago
      Sir ! Did you find solution of your problem then please share it with me.I am also facing the same issue...I am new in this...Please
  • To post as a guest, your comment is unpublished.
    Shiva · 11 months ago
    Private Sub Worksheet_Change(ByVal Target As Range)
    'Update 20140722
    Dim WorkRng As Range
    Dim Rng As Range
    Dim xOffsetColumn As Integer
    Set WorkRng = Intersect(Application.ActiveSheet.Range("A:A"), Target)
    xOffsetColumn = 1
    If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
    If Not VBA.IsEmpty(Rng.Value) Then
    Rng.Offset(0, xOffsetColumn).Value = Now
    Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
    Else
    Rng.Offset(0, xOffsetColumn).ClearContents
    End If
    Next
    Application.EnableEvents = True
    End If
    End Sub

  • To post as a guest, your comment is unpublished.
    Shiva · 11 months ago
    1 26-02-2021, 11:32:49
    1 26-02-2021, 11:32:49
    1 26-02-2021, 11:32:49
    1 26-02-2021, 11:32:49
    1 26-02-2021, 11:32:50
    1 26-02-2021, 11:32:50
    1 26-02-2021, 11:32:50
    1 26-02-2021, 11:32:50
    1 26-02-2021, 11:32:50
    0 26-02-2021, 11:33:04
    0 26-02-2021, 11:33:04
    0 26-02-2021, 11:33:04
    0 26-02-2021, 11:33:04
    0 26-02-2021, 11:33:04
    0 26-02-2021, 11:33:04
    0 26-02-2021, 11:33:04
    0 26-02-2021, 11:33:04
    0 26-02-2021, 11:33:04
    0 26-02-2021, 11:33:04

    Why on 0 value its getting date.. when cell have data then only get date otherwise show balnk...how we can do it vba code

  • To post as a guest, your comment is unpublished.
    MOAMB · 11 months ago
    Hi,
    The code works perfect but if changed the data in A i was wondering if i could have the date in column B and the time in Column D?
    Thanks for any help.
    • To post as a guest, your comment is unpublished.
      Narasimha · 10 months ago
      Private Sub Worksheet_Change(ByVal Target As Range)
      'Update 20140722
      Dim WorkRng As Range
      Dim Rng As Range
      Dim xOffsetColumn As Integer
      Set WorkRng = Intersect(Application.ActiveSheet.Range("J:J"), Target)
      xOffsetColumn = 1
      If Not WorkRng Is Nothing Then
      Application.EnableEvents = False
      For Each Rng In WorkRng
      If Not VBA.IsEmpty(Rng.Value) Then
      Rng.Offset(0, xOffsetColumn).Value = Now
      Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
      Else
      Rng.Offset(0, xOffsetColumn).ClearContents
      End If
      Next
      Application.EnableEvents = True
      End If
      xOffsetColumn = 2
      If Not WorkRng Is Nothing Then
      Application.EnableEvents = False
      For Each Rng In WorkRng
      If Not VBA.IsEmpty(Rng.Value) Then
      Rng.Offset(0, xOffsetColumn).Value = Now
      Rng.Offset(0, xOffsetColumn).NumberFormat = "hh:mm:ss"
      Else
      Rng.Offset(0, xOffsetColumn).ClearContents
      End If
      Next
      Application.EnableEvents = True
      End If
      End Sub
  • To post as a guest, your comment is unpublished.
    CV · 11 months ago
    Hi,
    If i were to have 2 columns duplicating the same macros, how can that be possible.
    I want to show the date and time in column B for values changed in A; while also wanting to show the time and date in column D for values changed in C.
  • To post as a guest, your comment is unpublished.
    Phoebe · 1 years ago
    Hi Extend Office, thank you for guiding me.
    I am a beginner , i had this problem which i saw some of your faced.
    This was the situation
    For e.g

    You want your date and time shown in Column A when any other column of the same row B:AZ for example got their values changed.
    So heres my solution. Please correct me if the code has any issues. TIA.

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim WorkRng As Range
    Dim Rng As Range
    Dim xOffsetColumn As Integer
    Set WorkRng = Intersect(Application.ActiveSheet.Range("B:AZ"), Target)'B:AZ put your own columns
    'rownumber=Activecell.Row will not work because it it will locate the adjacent row after you press enter or when ur mouse click on other cells
    rownumber = Target.Row
    If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
    If Not VBA.IsEmpty(Rng.Value) Then
    Range("A" & rownumber).Value = Now 'gives A and the adjacent row number
    Range("A" & rownumber).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
    End If
    Next
    Application.EnableEvents = True
    End If
    End Sub



    Thank you.
  • To post as a guest, your comment is unpublished.
    Phoebe · 1 years ago
    Hi All, There had been questions from many including me trying to make any changed cell from for e.g columns B to AZ reflect the date and time at column A for example. Here's my solution. Btw, i am only a beginner. If you find that my solution is wrong, please correct me. Thank you in advance. Refer below:

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim WorkRng As Range
    Dim Rng As Range
    Dim xOffsetColumn As Integer
    Set WorkRng = Intersect(Application.ActiveSheet.Range("B:AZ"), Target) 'this will be your full range of the whole table.
    'why Target because you are selecting the current cell, if you use Activecell.Row, you will see the dates appear adjacent to the cell you entered or where your mouse had clicked< i got stuck here real bad'
    rownumber = Target.Row
    If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
    If Not VBA.IsEmpty(Rng.Value) Then
    Range("A" & rownumber).Value = Now
    Range("A" & rownumber).NumberFormat = "dd-mm-yyyy, hh:mm:ss"

    End If
    Next
    Application.EnableEvents = True
    End If
    End Sub


    Hope it helps. Thanks extendoffice genius for guiding me throughout :)
  • To post as a guest, your comment is unpublished.
    Mahitha · 1 years ago
    Once I execute this code it works as expected, but Undo function is disable. Is there any fix for that?

  • To post as a guest, your comment is unpublished.
    Solanki · 1 years ago
    Dear sir
    Here I need your help.
    As I want to change any of the cells (B2 to M2) then shows the date and time in A2, can we do this kind of formula ?
    How can we do ?

    Vishnu
  • To post as a guest, your comment is unpublished.
    Venkatesh · 1 years ago
    First of all thanks, this VBA code worked perfectly. But i need some additional clarification, could some one please help me on that.

    Example
    If i enter data on cell B2, current time should be stamped on both on C1 & C2.

    Please some one help me with this. Thanks in advance!
  • To post as a guest, your comment is unpublished.
    sneha · 1 years ago
    Thanks ! It worked in just 2 minutes. Thanks a lot !
  • To post as a guest, your comment is unpublished.
    Ben · 1 years ago
    Hello

    This is great, is it possible to only insert the date into Column A?


    I don't know how to do this.


    Thank you

    Ben
  • To post as a guest, your comment is unpublished.
    TJWEN1 · 1 years ago
    How do i apply this code if i only want the time stamp to change if a specific value is entered, So i only wnat it to record the date when I select green from a drop down?
  • To post as a guest, your comment is unpublished.
    Shirley Esau · 2 years ago
    How do I change this code so that it is triggered by a change in another cell caused by a formula?
  • To post as a guest, your comment is unpublished.
    Makmur hasanuddin · 2 years ago
    What if I want to save data in a single cell that is updated daily with the current date and time?
    Example:
    I have a transaction balance that changes every day on Cell D2
    I want the data to be in Cell B2 (Today). The next day I want Cell D2 when there is a change, I want the data to automatically be in Cell B3 (H + 1) without experiencing changes to Cell B2

    Please enlighten me
  • To post as a guest, your comment is unpublished.
    Shawn · 2 years ago
    It is actually not working with my drop down list, is there any solution to that?
  • To post as a guest, your comment is unpublished.
    Sergio · 2 years ago
    Great tutorial, exactly what I needed. Thanks for posting.
  • To post as a guest, your comment is unpublished.
    IanC · 2 years ago
    The code in "VBA code: Record date and time automatically when cell changes" above works well in my worksheet where I manually enter data in a cell. However, I have a "summary" worksheet in which cells automatically update their value based upon the values of cells on other worksheets in the same workbook, e.g. The formula in my cell B6 on my 'summary' worksheet might be ='Example'!F3. When the value in 'Example!F3 changes the summary sheet cell B6 automatically updates to a new value. I am trying to show a timestamp on the summary sheet when ever changes occur to save me having to search through numerous linked worksheet for changes. With the present VBA code nothing happens when the value on my 'summary' sheet updates from the data on the linked worksheet cells. Can you help with this please?
  • To post as a guest, your comment is unpublished.
    Hannes · 2 years ago
    Would it be possible to have the timestamp appear 5 rows down? Would need it in an specific cell and not the entire column.
    Column range is easy to change to specific cell but i don´t know how to get the timestamp in an specific cell :(
    Please help!
  • To post as a guest, your comment is unpublished.
    Hannes · 2 years ago
    Would it be possible to change on what row the timestamp is showed? I would like to have the timestamp 5 rows down.
    Tried xOffsetRows = 5 but that did not word, maybe that command does not exist :)
    Please help!
  • To post as a guest, your comment is unpublished.
    Cody · 3 years ago
    what about if i need to have column I, J, and K all trigger a date into column L? I have tried posting script 3 times, and changing the name, and column offset, but it never runs the second and third script.
  • To post as a guest, your comment is unpublished.
    Cody Baird · 3 years ago
    also, i had to change the first line of code to be different for each copy/paste.
    example
    Private Sub Worksheet_Change (ByVal Target As Range)

    the next is

    Private Sub Worksheet_Change1 (ByVal Target As Range)


    by doing so i eliminate an error Compile Error: Ambiguous name detected: Worksheet_Change.

    if i duplicate this name it causes this error, so i changed them by adding a 1, 2, 3 etc..
  • To post as a guest, your comment is unpublished.
    Cody · 3 years ago
    I am attempting to make this reference multiple columns and reflect the date of any change in these columns back to one column. example, if i change anything in I,J,or K I want the date updated to current in L. i have tried to copy this VB code 3 times, changing the target, and column application to be I:I and 3 J:J and 2, K:K and 1. only K:K will work to change L. thoughts?
    • To post as a guest, your comment is unpublished.
      chris mcc · 2 years ago
      Hello, i need this as well. Any Joy?
  • To post as a guest, your comment is unpublished.
    vincenttom123@gmail.com · 3 years ago
    I received Run-Time Error '1004'


    This is the VBA on my Excel:


    Private Sub Worksheet_Change(ByVal Target As Range)
    'Update 20140722
    Dim WorkRng As Range
    Dim Rng As Range
    Dim xOffsetColumn As Integer
    Set WorkRng = Intersect(Application.ActiveSheet.Range("I:I"), Target)
    xOffsetColumn = -3
    If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
    If Not VBA.IsEmpty(Rng.Value) Then
    Rng.Offset(0, xOffsetColumn).Value = Now
    Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
    Else
    Rng.Offset(0, xOffsetColumn).ClearContents
    End If
    Next
    Application.EnableEvents = True
    End If
    Set WorkRng = Intersect(Application.ActiveSheet.Range("I:I"), Target)
    xOffsetColumn = -5
    If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
    If Not VBA.IsEmpty(Rng.Value) Then
    Rng.Offset(0, xOffsetColumn).Value = Now
    Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mmm-yyyy"
    Else
    Rng.Offset(0, xOffsetColumn).ClearContents
    End If
    Next
    Application.EnableEvents = True
    End If
    Dim xRg As Range
    On Error Resume Next
    Set xRg = Intersect(Range("A:O"), Target)
    If xRg Is Nothing Then Exit Sub
    Target.Worksheet.Unprotect Password:="123"
    If xRg.Value <> mStr Then xRg.Locked = True
    Target.Worksheet.Protect Password:="123"
    End Sub


    Thank You
  • To post as a guest, your comment is unpublished.
    Vincent · 3 years ago
    I receive Run-time error '1004'

    This is the VBA on my excel:


    Private Sub Worksheet_Change(ByVal Target As Range)
    'Update 20140722
    Dim WorkRng As Range
    Dim Rng As Range
    Dim xOffsetColumn As Integer
    Set WorkRng = Intersect(Application.ActiveSheet.Range("I:I"), Target)
    xOffsetColumn = -3
    If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
    If Not VBA.IsEmpty(Rng.Value) Then
    Rng.Offset(0, xOffsetColumn).Value = Now
    Rng.Offset(0, xOffsetColumn).NumberFormat = "hh:mm:ss"
    Else
    Rng.Offset(0, xOffsetColumn).ClearContents
    End If
    Next
    Application.EnableEvents = True
    End If
    Set WorkRng = Intersect(Application.ActiveSheet.Range("I:I"), Target)
    xOffsetColumn = -5
    If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
    If Not VBA.IsEmpty(Rng.Value) Then
    Rng.Offset(0, xOffsetColumn).Value = Now
    Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mmm-yyyy"
    Else
    Rng.Offset(0, xOffsetColumn).ClearContents
    End If
    Next
    Application.EnableEvents = True
    End If
    Dim xRg As Range
    On Error Resume Next
    Set xRg = Intersect(Range("A:O"), Target)
    If xRg Is Nothing Then Exit Sub
    Target.Worksheet.Unprotect Password:="123"
    If xRg.Value <> mStr Then xRg.Locked = True
    Target.Worksheet.Protect Password:="123"
    End Sub



    Thanks
  • To post as a guest, your comment is unpublished.
    wbanew@gmail.com · 3 years ago
    Kalau munculnya waktu minta di bawah bagaimana?
  • To post as a guest, your comment is unpublished.
    Thyme2Cook · 3 years ago
    This is exactly what I needed. Since I needed a timestamp in column F to reflect any changes in columns A, B, C, D, or E in a row, I duplicated the code that many times and redefined the range to point to each of the columns within the different iterations of the code.

    Private Sub Worksheet_Change(ByVal Target As Range)
    'Update 20140722
    Dim WorkRng As Range
    Dim Rng As Range
    Dim xOffsetColumn As Integer

    Set WorkRng = Intersect(Application.ActiveSheet.Range("A:A"), Target)
    xOffsetColumn = 5
    If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
    If Not VBA.IsEmpty(Rng.Value) Then
    Rng.Offset(0, xOffsetColumn).Value = Now
    Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
    Else
    Rng.Offset(0, xOffsetColumn).ClearContents
    End If
    Next
    Application.EnableEvents = True
    End If

    Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)
    xOffsetColumn = 4
    If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
    If Not VBA.IsEmpty(Rng.Value) Then
    Rng.Offset(0, xOffsetColumn).Value = Now
    Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
    Else
    Rng.Offset(0, xOffsetColumn).ClearContents
    End If
    Next
    Application.EnableEvents = True
    End If


    etc...
    • To post as a guest, your comment is unpublished.
      Tom · 2 years ago
      Thanks Chris, you saved me from a big headache!
    • To post as a guest, your comment is unpublished.
      MIke · 3 years ago
      Hi Chris, I see you posted the exact answer to my previous question already. I just had to expand the reading area. My bad. Thank you so much for your post. This has been so helpful.
    • To post as a guest, your comment is unpublished.
      Mike · 3 years ago
      Hi Chris, Can you let me know what part of the code I should copy and also where to paste it? I'm trying the same but am getting error codes. Preferably I would like to get a timestamp in Column F to reflect a change in Column A, and a timestamp in Column G to reflect a change in Column B. But one timestamp for a change in column A, B, C, D, or E like you did would already be great. Many thanks for your help.
  • To post as a guest, your comment is unpublished.
    Bonnie · 3 years ago
    Also, I have to say: Your instructions are THE BEST, so simple but effective and straight forward!!!

    by simply adding Rng.Offset(0, yOffsetColumn).Value = Environ$("UserName")

    above Else in the next column I am capturing the Windows Username as well, I didn't need a full Audit Trail and wanted the Change info WITH the data, this is perfect.


    Thank you so much!
  • To post as a guest, your comment is unpublished.
    Bonnie · 3 years ago
    I was wondering how would you modify the code to fill in the date only if you wanted it to populate for a given value. Say I only want the date to populate when I select "Closed" for a value for the target range?
  • To post as a guest, your comment is unpublished.
    amrsoliman8886@gmail.com · 3 years ago
    what can i do if i need to add another range in same sheet
    when i fill in column A reflect date & time in column B and when filling in column C reflect date & time in column D
    and when i clear the content of column A or C it clear B or D too

    please help me in this
    • To post as a guest, your comment is unpublished.
      Anto. · 3 years ago
      Me too expecting for the same pl help
  • To post as a guest, your comment is unpublished.
    Amr Soliman · 3 years ago
    What if i need to another range in same sheet?
    When i fill in column A reflect date&time in column B and when i fill in column C reflect date&time in column D
    and when i clear of any column A or C it clear B & D
  • To post as a guest, your comment is unpublished.
    J Hayes · 3 years ago
    This was very helpful. Thank you! :-)
  • To post as a guest, your comment is unpublished.
    shiva · 3 years ago
    how to make this work, if there is updated in E:E range as well?
    • To post as a guest, your comment is unpublished.
      Amr Soliman · 3 years ago
      did you get the way to do this?
  • To post as a guest, your comment is unpublished.
    Asela · 3 years ago
    Here is the macro to update Modified date to column F on each row.

    ------------------------------------------------

    'This Macro has been written to update Last modified date/time on each A2:D43415
    'Last Modified date applied to column F.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rInt As Range
    Dim rCell As Range
    Dim tCell As Range
    Dim tColInt As Integer

    tColInt = 6 'Column Index, Example: A=1, B=2, ...... ,Z=26


    Set rInt = Intersect(Target, Range("A2:D43415")) 'Change cell range
    If Not rInt Is Nothing Then
    For Each rCell In rInt
    Set tCell = Cells(rCell.Cells.Row, tColInt)
    If IsEmpty(tCell) Or Not IsEmpty(tCell) Then
    tCell = Now
    tCell.NumberFormat = "dd/mm/yyyy h:mm:ss AM/PM" 'Custom Format
    End If
    Next
    End If
    End Sub
    • To post as a guest, your comment is unpublished.
      Solanki · 1 years ago
      Thank you very much, here my problem solve
      But saving and closing the file automatically removes the micro!

      Thanks
    • To post as a guest, your comment is unpublished.
      Manuel Macias · 2 years ago
      brilliant, thank you
  • To post as a guest, your comment is unpublished.
    Juttskt · 3 years ago
    example
    Set WorkRng = Intersect(Application.ActiveSheet.Range("E:E,F:F,G:G,H:H"), Target)
    xOffsetColumn = 8
    i want any entry change in column E, F, G, H then date can stamp column M

    please help me in this
  • To post as a guest, your comment is unpublished.
    murdikayasa · 3 years ago
    Hi all, the code is working fine but i does not work when the worksheet is protected. although I have unprotected the column the certain column for the timestamp is. i am attaching the error message pop up. please help.

    Private Sub Worksheet_Change(ByVal Target As Range)
    'Update 20140722
    Dim WorkRng As Range
    Dim Rng As Range
    Dim xOffsetColumn As Integer
    Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)
    xOffsetColumn = 1
    If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
    If Not VBA.IsEmpty(Rng.Value) Then
    Rng.Offset(0, xOffsetColumn).Value = Now
    Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
    Else
    Rng.Offset(0, xOffsetColumn).ClearContents
    End If
    Next
    Application.EnableEvents = True
    End If
    End Sub
    • To post as a guest, your comment is unpublished.
      ChoppaChewy · 2 years ago
      You can unlock the sheet before and after the if to make it work:


      Private Sub Worksheet_Change(ByVal Target As Range)
      'Update 20140722
      Dim WorkRng As Range
      Dim Rng As Range
      Dim xOffsetColumn As Integer
      Set WorkRng = Intersect(Application.ActiveSheet.Range("A:A"), Target)
      xOffsetColumn = 1
      If Not WorkRng Is Nothing Then



      ActiveSheet.Unprotect



      Application.EnableEvents = False
      For Each Rng In WorkRng
      If Not VBA.IsEmpty(Rng.Value) Then
      Rng.Offset(0, xOffsetColumn).Value = Now
      Rng.Offset(0, xOffsetColumn).NumberFormat = "hh:mm"
      Else
      Rng.Offset(0, xOffsetColumn).ClearContents
      End If
      Next
      Application.EnableEvents = True



      ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
      False, AllowFiltering:=True



      End If
      End Sub
  • To post as a guest, your comment is unpublished.
    I Wayan Murdikayasa · 3 years ago
    Hi all,

    i use the code given and it works perfectly. but it does not working whenever the sheet is locked, although, i already unlocked the column form the time stamp is. can you help? thank you in advance.
  • To post as a guest, your comment is unpublished.
    Ali · 3 years ago
    Plz Reply


    Every thing is perfect ...every time i save the script but when i close the application and reopen it the script does not work and i need to write/copy it again.
    thanx
  • To post as a guest, your comment is unpublished.
    Ali · 3 years ago
    Every thing is perfect ...every time i save the script but when i close the application and reopen it the script does not work and i need to write/copy it again.
    thanx
  • To post as a guest, your comment is unpublished.
    iHickUp · 3 years ago
    Hello,


    Is it possible to implement this code with the data refresh. Because when i refresh the data it always puts the now stamp, however when the data is changed manually it does it okay and only records when manually changed correctly so all the times are then different, but when refreshed it might create new rows and therefore it puts all the current timestamps.
  • To post as a guest, your comment is unpublished.
    bterri@umflint.edu · 3 years ago
    How do I get this code to put the date stamp in the column to the left? I tried -1 and it didn't work. Also, it doesn't seem to recognize data filled in by a formula. Can I do that? I need this for twelve different columns in one Summary sheet that gathers data from six departmental sheets. Any help would be greatly appreciated.
  • To post as a guest, your comment is unpublished.
    srirevathi555@gmail.com · 4 years ago
    Hi, Can anyone one suggest a code for when I input number in column A and Column B and in Column C I kept a formula like Column A + Column B. Now I need a vba code that can give time and date in Column D whenever Column C changes not when inserting numbers in Column A and B.
    • To post as a guest, your comment is unpublished.
      Faizi · 3 years ago
      Have you solved this issue
      • To post as a guest, your comment is unpublished.
        sandeshmurchy@gmail.com · 3 years ago
        Hi Has any one got a solution for this?

        I have the same challenge.
  • To post as a guest, your comment is unpublished.
    Signat · 4 years ago
    Hello,
    How to define exact column for timestamp instead offset ?
  • To post as a guest, your comment is unpublished.
    Gabesz · 4 years ago
    Hi,
    the code runs perfectly, this is what I was searching for. One little update / help needed:
    I have the data in column B, the time is in column AA, and have another data in column A. It would be great if you could modify the code, so it will run only if the values in B and A are matching.

    Thanks in advance.
    • To post as a guest, your comment is unpublished.
      Gabesz · 4 years ago
      I have figured out. It's already down
      • To post as a guest, your comment is unpublished.
        iHickUp · 3 years ago
        Hello, could you share how you done it?
        • To post as a guest, your comment is unpublished.
          Gabesz · 3 years ago
          Hello there,
          Update the original code with that.
          In the meanwhile I have put another IF statement update before the code " If Rng.Offset(0, 1).Value = 0 Then ", because whenever you delete an upper row, it will cause cell value change, an will stamp the time again.

          If Rng.Offset(0, 1).Value = 0 Then
          If Not VBA.IsEmpty(Rng.Value) And Rng.Offset(0, -1).Value = Rng.Offset(0, 0).Value Then

          Hope it helped to you. :)
  • To post as a guest, your comment is unpublished.
    Gábor · 4 years ago
    Hi,
    the code works perfectly, this is what I was searching for. Thanks.
    One more little help / update about the code. I have the data in column B, the date+time is in AA, and another data in column A. Can you please help to modify the code, so it will run only if value in B and A is the same.

    Thanks in advance
  • To post as a guest, your comment is unpublished.
    RRexwinkle · 4 years ago
    I am having trouble getting the code to do what I want and think I am missing a step.



    I have a spreadsheet that I would like TODAY returned into Column N whenever an "X" is placed in either Column O or P, and return to blank if that "X" is deleted. What am I missing in code that will allow me to have that happen?
  • To post as a guest, your comment is unpublished.
    Hari · 4 years ago
    Tried code. Worked perfect in normal cases. But i need it little more advanced.
    I need the time & date populated in column F; keeping the columns from A to E uneditable.
    Can you help?
    • To post as a guest, your comment is unpublished.
      MP · 4 years ago
      xOffsetColumn = 5