I have an Excel worksheet detailing the status of invoices. Each invoice has its own column (range xx4:xx23). I want to have cell xx24 in each column include a date which changes when any cell in range xx4:xx23 is modified. So far I have this, which works:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E4:E23")) Is Nothing Then
'Stop any possible runtime errors and halting code
On Error Resume Next
'Turn off ALL events so the Target * 2 does not _
'put the code into a loop.
Application.EnableEvents = False
Cells.Range("E24") = Format(Date, "dd-mmm-yy") & " at " & Format(Time, "hh:mm")
'Turn events back on
Application.EnableEvents = True
'Allow run time errors again
On Error GoTo 0
However, the number of columns in the worksheet is potentially infinite, and I want a date modified cell in each column, including new columns when they are added. What I don't want is to have to add a new bit of VBA each time I add a new column.
Is there a way of modifying my script so that it inserts the date modified in cell xx24 for each non-empty column when that column is modified? Or is there a way of adding to my 'copyColumn' script (below) so that it duplicates the 'last updated' script for that column at the time of inserting a new column?
Select Case Sheets("Sheet1").Range("A1") = ""
Case True 'paste in col A if A1 is empty
Case False ' paste to next col
Sheets("Sheet1").Range("IV4").End(xlToLeft).Offset(0, 1).ColumnWidth = 19
Sheets("Sheet1").Range("IV4").End(xlToLeft).Offset(0, 1).PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False