+ Reply to Thread
Results 1 to 9 of 9

Code to show when a cell was updated, for several columns

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    09-06-2011
    Location
    manchester england
    MS-Off Ver
    Office 365
    Posts
    283

    Code to show when a cell was updated, for several columns

    I have this code which will show when a cell has been updated in column P.

    I would like it to also update for columns C, K, P & Q Too.

    Is that possible?

    Any help appreciated.

    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("p:p"), Target)
    xOffsetColumn = 15
    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

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,589

    Re: Code to show when a cell was updated, for several columns

    Try

    Set WorkRng = Intersect(Application.ActiveSheet.Range("c:c","k:k","p:p","q:q"), Target)
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  3. #3
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,278

    Re: Code to show when a cell was updated, for several columns

    Possibly...
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Select Case Target.Column
            Case Is = 3, 11, 16, 17
                Application.EnableEvents = False
                With Cells(Target.Row, 30)
                    .Value = Now
                    .NumberFormat = "dd-mm-yyyy, hh:mm:ss"
                End With
                Application.EnableEvents = True
        End Select
    
        
    End Sub

  4. #4
    Forum Contributor
    Join Date
    09-06-2011
    Location
    manchester england
    MS-Off Ver
    Office 365
    Posts
    283

    Re: Code to show when a cell was updated, for several columns

    Thank you I will try those

  5. #5
    Forum Expert
    Join Date
    10-11-2021
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    1,408

    Re: Code to show when a cell was updated, for several columns

    One more variant

    Private Sub Worksheet_Change(ByVal Target As Range)
     With Application
       If IsNumeric(.Match(Target.Column, Array(3, 11, 16, 17), 0)) Then
         .EnableEvents = False
          Cells(Target.Row, 30).Value = Format(Now, "dd-mm-yyyy, hh:mm:ss")
         .EnableEvents = True
       End If
     End With
    End Sub

  6. #6
    Forum Contributor
    Join Date
    09-06-2011
    Location
    manchester england
    MS-Off Ver
    Office 365
    Posts
    283

    Re: Code to show when a cell was updated, for several columns

    Thanks all for the help, they do work.

    However I want the chnages to show as independant.

    So at the moment if I change column c or p for example the time and date updates in the column.

    What I want it to do is show a column update for each individual column, so if c or p change on a different date i can track it.

    Does that make sense?

  7. #7
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,278

    Re: Code to show when a cell was updated, for several columns

    Possibly...
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Select Case Target.Column
            Case Is = 3, 11, 16, 17
                Application.EnableEvents = False
                With Cells(Target.Row, 30 + Target.Column)
                    .Value = Now
                    .NumberFormat = "dd-mm-yyyy, hh:mm:ss"
                End With
                Application.EnableEvents = True
        End Select
        
    End Sub

  8. #8
    Forum Contributor
    Join Date
    09-06-2011
    Location
    manchester england
    MS-Off Ver
    Office 365
    Posts
    283

    Re: Code to show when a cell was updated, for several columns

    Thank you.

    That is perfect and adaptable now

  9. #9
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,278

    Re: Code to show when a cell was updated, for several columns

    Glad I could help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA Code will not update as target cell is updated using a formula
    By neilcsmith in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-05-2020, 09:04 PM
  2. [SOLVED] VBA code to show updated data in the listbox after the data is entered and button clicked
    By chin67326 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-25-2015, 04:23 AM
  3. [SOLVED] why does the wrong cell value get updated with this code
    By yoursamrit2000 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-27-2014, 09:12 PM
  4. Auto run the Code automaticaly when cell is updated
    By tharindudk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-16-2013, 12:11 AM
  5. Replies: 5
    Last Post: 12-06-2013, 08:22 AM
  6. Replies: 0
    Last Post: 06-20-2012, 10:58 AM
  7. Replies: 2
    Last Post: 12-28-2006, 02:58 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1