Results 1 to 14 of 14

Date Stamp within Excel table - Changes in multiple rows and columns and new rows added

Threaded View

  1. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Date Stamp within Excel table - Changes in multiple rows and columns and new rows adde

    Part One:-
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    A = Split(Replace(Target.Address, ":", ""), "$")
    
    If A(1) = "A" And A(3) = "A" Then Range(Cells(A(2), 5), Cells(A(4), 5)).Value = Date
    
    End Sub

    Part 2:-
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If InStr(Target.Address, ":") = 0 Then
    A = Target.Address & ":" & Target.Address
    Else
    A = Target.Address
    End If
    
    B = Split(A, ":")
    B1 = Range(B(0)).Column
    B2 = Range(B(1)).Column
    
    If B1 > 1 And B1 < 5 And B2 > 1 And B2 < 5 Then Range(Cells(Range(B(0)).Row, 6), Cells(Range(B(1)).Row, 6)).Value = Date
    
    End Sub
    Part3:
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    
    If InStr(Target.Address, ":") = 0 Then
    A = Target.Address & ":" & Target.Address
    Else
    A = Target.Address
    End If
    
    B = Split(Replace(A, ":", ""), "$")
    
    For Count = B(2) * 1 To B(4) * 1
    If Cells(Count, 7) = "" Then Cells(Count, 7) = Date
    Next
    Application.EnableEvents = True
    End Sub


    So putting those together and tidying up:-

    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Application.EnableEvents = False
    
    If InStr(Target.Address, ":") = 0 Then
    A = Target.Address & ":" & Target.Address
    Else
    A = Target.Address
    End If
    
    If A(1) = "A" And A(3) = "A" Then Range(Cells(A(2), 5), Cells(A(4), 5)).Value = Date
    
    B = Split(A, ":")
    B1 = Range(B(0)).Column
    B2 = Range(B(1)).Column
    
    If B1 > 1 And B1 < 5 And B2 > 1 And B2 < 5 Then Range(Cells(Range(B(0)).Row, 6), Cells(Range(B(1)).Row, 6)).Value = Date
    
    C = Split(Replace(A, ":", ""), "$")
    
    For Count = C(2) * 1 To C(4) * 1
    If Cells(Count, 7) = "" Then Cells(Count, 7) = Date
    Next
    Application.EnableEvents = True
    
    End Sub
    Last edited by mehmetcik; 03-04-2016 at 06:48 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 6
    Last Post: 02-23-2024, 02:11 PM
  2. [SOLVED] Insert rows in linked table that are added to secondary table
    By Ida LaValley in forum Excel General
    Replies: 4
    Last Post: 11-16-2020, 12:00 PM
  3. Replies: 3
    Last Post: 02-17-2014, 09:20 PM
  4. Replies: 1
    Last Post: 11-29-2012, 06:24 PM
  5. Replies: 2
    Last Post: 11-03-2009, 03:30 PM
  6. Get data from duplicate rows into added columns
    By Lithium in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-08-2009, 02:19 PM

Tags for this Thread

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