Excel macro : to comment history with date stamp

[Randheer]

Thanks in advance for your help.


I am looking for a macro/function that does below:
Column 1 : Has comments that I add in each day
Column 2 : Maintain the history of all comments form the past


When I add a comment in Column 1 , And when I press a button or automatically when I go away from the cell -> the function copies the comment from column 1, appends to the top of column 2 with today date stamp.


Is this easy to achieve?

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this is any use. Test in a copy of your workbook.
For any cells altered in column A (I have excluded cell A1), the new value should be entered at the top of column B, Date at top of col C and the cell address in col D
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Columns("A"), Rows("2:" & Rows.Count))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      Range("B1:D1").Insert Shift:=xlDown
      Range("B1").Value = c.Value
      Range("C1").Value = Date
      Range("D1").Value = c.Address(0, 0)
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[Randheer]

Thanks @Peter_SSs for the prompt response. Really appreciate it!

Just to clarify a bit more on the requirement. It wanted it to to look something like below table. When I finish typing the comment 4 in the 2 rows. It will append at the top of the column 2 in respective rows , with correct date/time stamp. Apologies for not being clear earlier.

Column1 <tbody> </tbody>	Column2 <tbody> </tbody>
Comment4,Row2 <tbody> </tbody>	15.10/2017: 11:42: Comment3,Row2 14.10/2017: 11:43: Comment2,Row2 14.10/2017: 11:42: Comment1,Row2
Comment4,Row3 <tbody> </tbody>	15.10/2017: 10:00: Comment3,Row3 12.10/2017: 11:00: Comment2,Row3 11.10/2017: 09:42: Comment1,Row3

<tbody>
</tbody>

 

[Peter_SSs]

OK, I understand a bit better now. Replace that previous code with this one. make sure that you have column B set to Wrap Text.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range

  Set Changed = Intersect(Target, Columns("A"), Rows("2:" & Rows.Count))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      With c.Offset(, 1)
        .Value = Format(Now, "dd.mm/yyyy: hh:mm: ") & c.Value & IIf(Len(.Value) = 0, "", vbLf) & .Value
      End With
    Next c
    Application.EnableEvents = True
  End If
End Sub

Here is my sheet after I have made some entries (& deletions).

Book1
	A	B
1	Comments	History
2
3	These were all entered at once with Ctrl+Enter	15.10/2017: 22:23: These were all entered at once with Ctrl+Enter
4	These were all entered at once with Ctrl+Enter	15.10/2017: 22:23: These were all entered at once with Ctrl+Enter
5	These were all entered at once with Ctrl+Enter	15.10/2017: 22:23: These were all entered at once with Ctrl+Enter
6
7	But now I have entered a couple more values	15.10/2017: 22:32: But now I have entered a couple more values 15.10/2017: 22:27: The blank below was because the cell value was deleted 15.10/2017: 22:26: 15.10/2017: 22:26: Another comment here 15.10/2017: 22:23: Comment2 15.10/2017: 22:23: Comment1
8		15.10/2017: 22:27:
9
10	This group	15.10/2017: 22:25: This group
11	of cells	15.10/2017: 22:25: of cells
12	was pasted	15.10/2017: 22:25: was pasted
13	from elsewhere	15.10/2017: 22:25: from elsewhere
14
15
16		15.10/2017: 22:30: 15.10/2017: 22:30: This comment was entered and then deleted
17
Comment History

 

[Randheer]

Thanks a lot Peter . This works

 

[Peter_SSs]

Thanks a lot Peter . This works

Cheers.