Tom’s Tutorials For Excel: Using a Comment to Log Changes in a Cell
Here’s an easy way to keep a running log of changes to a cell’s text.
Suppose you want your employees to enter an explanation or description of some kind into a cell regarding a topic on your spreadsheet. Maybe there’s a new product being developed and you’ll utilize cell A1 for the team members to enter their ideas during production.
You want to keep a record of everything entered, without burdening anyone with how to edit existing text or how to add a new comment to a cell. The simpler, the better.
In the picture, over the course of time, new entries are made into cell A1. Although each new entry in A1 overrides pre-existing text, the below code will capture all the text ever entered. There’s also a date and time stamp for each new entry, and an empty line between entries for readability.
To install the code, right-click on your worksheet tab, left-click to select View Code, and paste the following procedure into the large white area that is the worksheet module. Then press Alt+Q to return to your worksheet, and test the code to see how the comment creates and updates itself with every change in text you make to cell A1.
Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Address <> "$A$1" Then Exit Sub If IsEmpty(Target) Then Exit Sub Dim strNewText$, strCommentOld$, strCommentNew$ strNewText = .Text If Not .Comment Is Nothing Then strCommentOld = .Comment.Text & Chr(10) & Chr(10) Else strCommentOld = "" End If On Error Resume Next .Comment.Delete Err.Clear .AddComment .Comment.Visible = False .Comment.Text Text:=strCommentOld & _ Format(VBA.Now, "MM/DD/YYYY at h:MM AM/PM") & Chr(10) & strNewText .Comment.Shape.TextFrame.AutoSize = True End With End Sub
I love this code! The only problem I have is that it is restricted to one specified cell. Is there any tweak that would allow this to work over a range of cells, say an entire column?
Say you want to use any cell in column C except for cell C1 which is a header row. Column C is the third column from the left, meaning it is regarded by VBA as Columns(3).
The very first line of code in that procedure would be
If Target.Column <> 3 Then Exit Sub.
Also, instead of a particular cell such as A1 in my example, rule out all of row 1 with this replacement code line:
If Target.Row = 1 Then Exit Sub
That should accomplish what I think you are asking about
I’ve been searching for two days now to find something not even perfect as your code and I couldn’t find any. Your code is awesome! I started tweaking it a bit to work for my need and so far so good. I just noticed a couple of things and since I’m not a VBA expert I’m not sure how to solve this.
1. How can I make it ignore the first entry in the cell? I want it to start tracking if I changed the first entry.
2. I noticed that once I enter a value in the cell, I can’t use “Undo”. So if I made mistake, I’ll have to re-enter the correct value which will result in an additional entry in the tracking comment.
3. This is the most important request. How can I make it record the old and the new value instead of the new one only? EX: A1 has the value 100 then I changed it to 200. I want to comment to be something like (04/29/2018: Changed from 100 to 200).
If you don’t mind, email me your PayPal ID so I can send a little gift appreciating your time and effort.
Hello Sameh, thank you for your kind comment.
Regarding your first question, you can place this block of code immediately after the procedure name:
Dim OldVal$, NewVal$OldVal = ""
Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
OldVal = Target.Value
Target.Value = NewVal
Application.EnableEvents = True
If OldVal = "" Then Exit Sub
Regarding your second question, Excel loses its Undo stack when a worksheet level procedure is executed. There is nothing that can be done about that.
Regarding your third question, your example would only be practical for the first change. When new data is entered in the target cell each time, it would be impractical to compile the log of changes any better than how I have those changes recorded in the comment. If your intention is to only care about, and reference, the last 2 changes, then that would defeat the purpose of keeping track of all the changes which was the basic objective of this code to begin with.
Thank you for offering payment but I supply Excel information on my blog at no charge. I will be developing interactive workbook examples with actual code and formulas that I’ll be putting on my website’s Store page later this year, for which there will be a small charge.
Hi Tom,
I managed to use the old value of the cell by using (OldVal) because I really need it in this worksheet and it worked but unfortunately, I noticed it doesn’t perceive the cell value format (EX: if the old value was $1000 it will be recorded in the comment as 1000 not $1000). Why is that?
How can I perceive the cell value format in the comment?
The format only makes a value look a certain way. Excel and VBA cares about what the actual underlying value really is. So if you are entering a number and you want it to look like currency, the code needs to format that value as the currency type you want, including decimal places and comma thousands separators, depending on how you want it to look.
Hi Tom. I think it’s more about the old value was saved as a variable in your code because the “new” value perceives the format just fine. (EX: if I changed the value from $2,000 to $1,000 the comment will be more like “Changed from 2000 to $1,000”. New value always perceives the cell format while the old value doesn’t. That what made me ask you. I figured if it’s working with the new value just fine why not the old one?
Hi Tom,
Thank you so much for your quick response. Your new code works perfect but unfortunately, now when I hit “Enter” after I add a value to a cell, Excel doesn’t behave like it normally behaves by moving the Focus to the cell below it. It stays focused on the same cell I’m currently editing. The rest of the worksheet behave normally. Just the column that we specify in the code works that way.
I added your new code right below “If Target.Column 3 Then Exit Sub”. I also tried it below “If IsEmpty(Target) Then Exit Sub”. In both cases, it works just fine but still behaves the same way by staying focused in the cell I’m editing.
I know you offer the codes and the help for free but I want to send a gift as an appreciation for your time and effort. Also, by saving me a lot of time after using this awesome code. Please, I insist.
Hello Sameh, thank you for again offering payment, but I have answered thousands of Excel questions like this over the years, on my website here and on various Excel message boards, and I do it as a free service. I will be selling Excel tools later this year on my website, so how about I email you when my Store page is ready.
Now to your question, this line of code…
Target.Offset(1).Activate…should be inserted in 2 places.
One place is immediately after the line
Application.EnableEvents = Trueand the other place is at the very end, immediately above the line
End SubTherefore, this would be the full procedure for your purposes:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldVal$, NewVal$
OldVal = ""
Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
OldVal = Target.Value
Target.Value = NewVal
Application.EnableEvents = True
Target.Offset(1).Activate
If OldVal = "" Then Exit Sub
With Target
If .Address <> "$A$1" Then Exit Sub
If IsEmpty(Target) Then Exit Sub
Dim strNewText$, strCommentOld$, strCommentNew$
strNewText = .Text
If Not .Comment Is Nothing Then
strCommentOld = .Comment.Text & Chr(10) & Chr(10)
Else
strCommentOld = ""
End If
On Error Resume Next
.Comment.Delete
Err.Clear
.AddComment
.Comment.Visible = False
.Comment.Text Text:=strCommentOld & _
Format(VBA.Now, "MM/DD/YYYY at h:MM AM/PM") & Chr(10) & strNewText
.Comment.Shape.TextFrame.AutoSize = True
End With
Target.Offset(1).Activate
End Sub
Thanks again for the help. It worked. Now when I try to save my file it pops up the following error:
“The following features cannot be saved in macro-free workbooks:
– VB project”
I remember yesterday I saved the file normally like I do without receiving this error but I might be wrong. Is this a must? Should I save it as xlsm file or there is any other way around?
Sorry for all the questions.
For a workbook containing programming code, it must be saved as a .xlsm (macro enabled) extension to have the programming code to execute.
Tom, can I make it add the comment to a different cell other than the one that has been changed?
EX: If the value in D2 changed, the comment will be added to B2 instead of D2?
Try changing the line
With Target
to
With Target.offset(0, -2)
where appropriate.
I made this change but it shows me an error for this line With Target.offset(0,-2)
Post your entire event code.
Managed to do the change successfully. I will post my final code once I finish for your review.
Thanks Boss for your timeless support
Hi Tom,
thanks for the code it’s work great except i noticed the code does not work if i drag down data in more than one box at the same time do you know how to fix this ?
This line…
[code]If .Address <> “$A$1” Then Exit Sub[/code]
…limits the example to changes to, and a comment for, cell A1.
The example code I posted would not do what you are asking about. You’d need a different code to identify all the cells that were dragged onto (hence changed), and loop through those cells to update their respective comments. I suggest asking that on an Excel forum. Your question gives me an inspiration for a future blog post that I can do later this year.
Thanks for your answer
i already remove that line
[code]If .Address “$A$1” Then Exit Sub[/code]
to make it work everywhere in the sheet but of course i still have the drag down problem
also i suggest to add username with this modification to the code:
[code]”Set to ” & strNewText & ” On ” & Format(VBA.Now, “MM/DD/YYYY at h:MM AM/PM”) & ” by ” & Application.UserName & Chr(10)[/code]
As I said, it’s not just that one line of code. Each cell that was dragged onto needs to be looped through so that each value in each changed cell can be represented in that cell’s respective comment.
Tom this is great!!! Just what I was looking for!!!
Just one question. Is there a way to have the comment add the original data that was in the cell (1st entered data) to track the audit trail?
Thanks, Jason.
As to your question, isn’t that what the code already does? See the pictured sequence where every edit to the cell, including the cell’s first entry, is recorded and shown in the comment after it (the first entry) is made and after each change thereafter.
When I add the first entry and change it, example: 123456 changes to 987654, it will start the capture at 987654 and not say the first entry was 123456.
Something else is going on here.
As you can see in the picture, the first cell entry showed up in the comment.
Maybe you are entering something into a cell before you have installed the event code, and afterwards, with data already in a cell, you have installed the event code. Is that what is going on?
This is the code I am using, “to not start tracking until the original data is changed”. Is there a way to have it also track what was originally there? The first entry?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldVal$, NewVal$
OldVal = “”
Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
OldVal = Target.Value
Target.Value = NewVal
Application.EnableEvents = True
Target.Offset(1).Activate
If OldVal = “” Then Exit Sub
With Target
If Target.Cells.CountLarge = 1000 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
Dim strNewText$, strCommentOld$, strCommentNew$
strNewText = .Text
If Not .Comment Is Nothing Then
strCommentOld = .Comment.Text & Chr(10) & Chr(10)
Else
strCommentOld = “”
End If
On Error Resume Next
.Comment.Delete
Err.Clear
.AddComment
.Comment.Visible = False
.Comment.Text Text:=strCommentOld & _
Format(VBA.Now, “MM/DD/YYYY at h:MM AM/PM”) & ”by” & Application.UserName & Chr(10) & strNewText
.Comment.Shape.TextFrame.AutoSize = True
End With
Target.Offset(1).Activate
End Sub
There is an easy workaround to what you are asking about.
When I originally posted this example, the idea was to have the code in place before anything was entered into cells that the code is monitoring.
What you are asking about is how to get around the situation where data exists in a cell, and THEN you install the code.
In that case, all you need to do AFTER data was first entered, and AFTER you install the code, but BEFORE you change the original cell’s contents, is to select that cell, hit the F2 key and then hit Enter. That will trigger the Change event and you will have the full history of cell changes, starting with the first originally entered data.
And I agree with Sameh, there has to be a way for you to be compensated for this support! 🙂
That’s very kind, thank you.
I will be releasing my Excel test next month, named XAT (an acronym for Excel Aptitude Test), and the best compensation I can think of is to take a look at it (I will offer it at no charge for the first week or so) and then sing its praises wherever you may roam. Seriously, I am really excited about my test, it is the best Excel test anywhere offered by anyone, and I do mean by anyone including Microsoft’s and anyone’s. I’ll announce its release on my social media channels (Twitter, LinkedIn, and my Facebook business pages), so look for XAT in August!!
Thanks Tom, however what I am looking for is:
Cell is Empty (No Comment)
Data is Inputted (No Comment on First Data Entered)
Data is Changed (Shows Comment. What was there first and what it is now changed to.)
Data Changed Again (Continues to Show Audit Trail in Comment)
Everything ins the code works great, however its not showing what was entered first. Currently it is starting the comment on the first “change to” not “changed from to”.
Anyway of doing this?
Again thanks for the help in advance!!!
Still not clear but getting closer.
You wrote:
“Data is Inputted (No Comment on First Data Entered)
Data is Changed (Shows Comment. What was there first and what it is now changed to.)”
So what you are saying is you want to enter something in a cell for the first time but not make a comment to show that. But, after you change what was first entered, in other words, starting with the second entry to that cell, you want to begin the string of comments, starting with what was first in the cell followed by the first edit. That means 2 things:
(1) The first comment that shows up will have 2 entries in it: the original entry and the first edited entry.
(2) If (1) is correct, there would need to be a time and date stored somewhere for when the first entry was made. The comment code does that now for edits and changes, but not in consideration for what I think you are asking about.
You are 100% correct!!! 🙂
Is there anyway to do that?
– Data is added into a blank cell for the first time (do nothing) like it is now.
– Once that data has changed (pull in the original data into the comment to show what it “was”) and
start the audit trail from there?
Is there a particular cell, or a particular range of cells, where you want this to be the case.
I have a workbook with many of pages, so currently I have it added into all sheets and going through the cells to 1000. If Target.Cells.CountLarge = 1000 Then Exit Sub
No idea what this means as it relates to what you are asking about updating a comment:
If Target.Cells.CountLarge = 1000 Then Exit Sub
My question was this:
Is there a particular cell, or a particular range of cells, where you want this to be the case.
An answer to my question could be:
“Yes Tom, cell A1 only, on Sheet3.”
or
“Yes Tom, range J7:J25 on every sheet.”
or
“Yes Tom, all of row 38 on Sheet2 and Sheet5.”
or
“Yes Tom, every cell on every worksheet.”
So, please take another stab at reading my question and telling me an answer that I would comprehend from an Excel point of view.
Thanks Tom.
“Yes, range A1:K:100 on every sheet”
Or better yet, every cell every worksheet.
I have figured out a way to have the “previous” data show-up in the comments, however am looking to see if I can continue to add additional comments instead of replacing them. You first will have to go to “Tools” in VBA, “References” and put a Check Mark in “Microsoft Script Run Time”, then press Okay.
Can you look at this code and see how I can add additional comments, instead of replacing them?
Here is the code:
Dim xRg As Range
Dim xChangeRg As Range
Dim xDependRg As Range
Dim xDic As New Dictionary
Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldVal$, NewVal$
OldVal = “”
Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
OldVal = Target.Value
Target.Value = NewVal
Application.EnableEvents = True
If OldVal = “” Then Exit Sub
Dim I As Long
Dim xCell As Range
Dim xHeader As String
Dim xCommText As String
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False
xHeader = “Previous value :” & preValue & Chr(10) & “Revised ” & Format _
(Date, “mm-dd-yyyy”) & Chr(10) & “By ” & Application.UserName
For I = 0 To UBound(xDic.Keys)
Set xCell = Range(xDic.Keys(I))
If Not xCell.Comment Is Nothing Then xCell.Comment.Delete
With xCell
.AddComment
.Comment.Visible = False
.Comment.Text xHeader & vbCrLf & xDic.Items(I)
End With
Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim I, J As Long
Dim xRgArea As Range
On Error GoTo Label1
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Set xDependRg = Target.Dependents
If xDependRg Is Nothing Then GoTo Label1
If Not xDependRg Is Nothing Then
Set xDependRg = Intersect(xDependRg, Range(“A:Z”))
End If
Label1:
Set xRg = Intersect(Target, Range(“A:Z”))
If (Not xRg Is Nothing) And (Not xDependRg Is Nothing) Then
Set xChangeRg = Union(xRg, xDependRg)
ElseIf (xRg Is Nothing) And (Not xDependRg Is Nothing) Then
Set xChangeRg = xDependRg
ElseIf (Not xRg Is Nothing) And (xDependRg Is Nothing) Then
Set xChangeRg = xRg
Else
Application.EnableEvents = True
Exit Sub
End If
xDic.RemoveAll
For I = 1 To xChangeRg.Areas.Count
Set xRgArea = xChangeRg.Areas(I)
For J = 1 To xRgArea.Count
xDic.Add xRgArea(J).Address, xRgArea(J).Text
Next
Next
Set xChangeRg = Nothing
Set xRg = Nothing
Set xDependRg = Nothing
Application.EnableEvents = True
End Sub
Hi, many thanks for the code, it´s perfect for keeping a history! Is there any way to sort the new comment by date? Placing the most recent one on the top?
This variation should do what you want:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address <> "$A$1" Then Exit Sub
If IsEmpty(Target) Then Exit Sub
Dim strNewText$, strCommentOld$, strCommentNew$
strNewText = .Text
If Not .Comment Is Nothing Then
strCommentOld = .Comment.Text
Else
strCommentOld = ""
End If
On Error Resume Next
.Comment.Delete
Err.Clear
.AddComment
.Comment.Visible = False
.Comment.Text Text:=strNewText & Chr(10) & Format(VBA.Now, "MM/DD/YYYY at h:MM AM/PM") & Chr(10) & Chr(10) & strCommentOld
.Comment.Shape.TextFrame.AutoSize = True
End With
End Sub
Great Tom, really appreciated! Something last you know how to trigger this code when the target cell´s value is related to a cell to another worksheet?
I use this worksheet to concentrate data from different worksheets.
Thanks, Konstantinos; good to know you found the code useful.
What you are asking for is do-able, but can you clarify a few items:
(1) Is the target cell really on just one other worksheet, or more than one other worksheet.
(2) Is the target cell just one cell or more than one cell.
(3) Is/Are the change(s) to the target cell(s) caused by manual entry input, or by a change of returned values from a formula in that/those cell(s).
(4) What is the name of the worksheet where the comment is, what cell is it in, and what is/are the name(s) of the other worksheet(s).
Hi Tom,
Thank your for your prompt reply, here are my answers:
(1) More than one other worksheet.
(2) More than one cell.
(3) By a change of returned values from a formula in that/those cell(s).
(4) The name of the sheet where the comments are is “BOQ”, in range E7:E49, some of the rows are empty as they are used as headers.
The names of the other worksheets are:”ADAM”, “Cajas CP”, “Cajas SG”, “FYC”, “PMV”, “Servers” and “TOTAL”. More specifically the origin cells are:
• ADAM!A10
• Cajas CP!$E$31
• Cajas CP!C21
• Cajas CP!C22
• Cajas CP!C23
• Cajas CP!C24
• Cajas CP!C25
• Cajas CP!C26
• Cajas SG!$A$26
• Cajas SG!A23
• FYC!A5
• PMV!$J$102
• PMV!$J$103
• PMV!$J$104
• Servers!A8
• TOTAL!$B$20
• TOTAL!$B$23
• TOTAL!B11
• TOTAL!B15
• TOTAL!B3
• TOTAL!B4
• TOTAL!B6
Hope this is enough.
Those are a lot of cells on a lot of sheets to monitor, for a lot of comments.
So, all those cells on the source sheet change their returned values by formulas in those source sheet cells.
It is not clear which cell on which source sheet go to which destination cell’s comment on the BOQ sheet. There needs to be some mapping to tell VBA what to do.
I am slammed with end of the year projects for my clients so I will try to get to this, but with the holiday weekend and the beginning of a new business year, I cannot examine it before the second week of January.
I strongly recommend that if you need an answer sooner, you ask and provide this same info to a popular Excel forum that I am also a member of, at https://www.mrexcel.com/board/forums/excel-questions.10/. It is free and easy to become a member, no spam, and there are Excel experts around the clock who can help you this week and next week faster than I can. If you still need help after that, please post back and I will do my best to look at it in January.
If I create another column in “BOQ” next to E7:E49, let’s say F7:F49. In this second range i could link the values from the first range, This would make easier to monitor the source since they would be all together and in the same worksheet.
I’ll try the forum you sent me:)
Thanks again, enjoy your holiday!
Hi Tom,
Happy new year. I tried to find a solution but with no luck. Do you believe that if the cells to monitor where in a column next to E7:E49, would make the code easier?
In this column (e.g F7:F49) I can link the values from the other sheets.
Thanks
Kostas
If you mean monitoring simultaneous changes to all those cells in F7:F49, then you are in the same boat as before, where you need to loop through each of those cells all the time and individually update the comments. But now you raise a different factor when you said “link the values” which probably means formulas in those cells, which further means utilizing a Calculation Change event instead of just a Change event.
I do not want to leave you with the impression that what you are asking for cannot be done, and by that I mean doing a “batch update” when you want to copy and paste a bunch of cells at the same time. It can be done. I just don’t have the ability to get to that with my client projects and deadlines at this time. If you are stone cold determined that that is what you want, I suggest asking people on the mrexcel forum I posted previously. But, I still say that even though it is possible to do, I would look at different designs or approaches for updating numerous comments at the same time.
Thank you for your efforts Tom,
It would be great to have formulas to trigger this event and not manually changing the values of the cells. Anyway I´ll keep searching if a can find a workaround and let you know.
Thanks again, great code!
Hi Tom,
Here are my answers:
(1) More than one sheet.
(2) More than one cell.
(3) By a change of returned values from a formula in those cells.
(4) The comments are in the worksheet named “BOQ” in range B7:B49 (with some empty rows in between that are been used as headers).
The other worksheets where the values are coming from are “ADAM”, “Cajas CP”, “Cajas SG”, “FYC”, “PMV”, “Servers” and “TOTAL”. More specifically the cells are:
•ADAM!A10
•Cajas CP!$E$31
•Cajas CP!C21
•Cajas CP!C22
•Cajas CP!C23
•Cajas CP!C24
•Cajas CP!C25
•Cajas CP!C26
•Cajas SG!$A$26
•Cajas SG!A23
•FYC!A5
•PMV!$J$102
•PMV!$J$103
•PMV!$J$104
•Servers!A8
•TOTAL!$B$20
•TOTAL!$B$23
•TOTAL!B11
•TOTAL!B15
•TOTAL!B3
•TOTAL!B4
•TOTAL!B6
Hope the info is enough.
Replied to the duplicated comment.
Hello Tom
Going off your original code, how would I change the code to:
-work on a range of cells, eg(b6:n50)?
-have the target cell to have comment be the one that is being changed. eg. (cell d24 changed,
cell d24 has the comment inserted)
Been looking for something like this for awhile, thank you for the code
and any help.
Dave
With the original code I posted, replace this line
If .Address <> "$A$1" Then Exit Subwith this line
If Intersect(Target, Range("B6:N50")) Is Nothing Or Target.Cells.Count > 1 Then Exit SubThanks Tom easy enough.
Next question.
Would we be able to copy and paste multiple cells into the range (“B6:N50”)
at the same time and have each cell have a comment? Right now when I move multiple cells
into the range nothing gets a comment.
Thanks Dave
The reason for this stems from this part of the code:
Or Target.Cells.Count > 1
To do what you want is not as simple as just deleting that part of the code. What would be needed is to monitor and loop through potentially 585 cells and their comments. I do not recommend doing it that way, using an event as the trigger, but a macro to do what you want as an update. Today I am slammed with client projects but I suggest you think about your project and why you would copy and paste multiple cells for this need of modifying comment logs.
I’m a Pricing Coordinator at a Grocery store. I use excel to keep track of
order #’s, its price and what store it went to. My comments will be used for history
tracking of when a price was created/updated.
Order #’s are entered into a “temp area” on a sheet and once I have set a price and a tag
has been generated, all items in the temp area are moved over into a “done area” where
your code will be active.
When I have hundred or so items with price attached, thats 200 cells to move one at a time
or copy and paste them over and then manually running thru the list to re-enter the price to
get the comment insert.
I’m not comfortable with running macro’s yet, as my ranges are constantly growing and dont
want to miss cells or mess something up.
I do thank you for help and prompt responses. The code I have now will be very helpful and
usefully.
Thanks again
Dave
Hello Tom
Thanks for this code. Only I point , I want to add user name also under tracking cell comment to check who changed the cell in shared sheet.
What command to be changed?
Try changing this:
strCommentOld = .Comment.Text & Chr(10) & Chr(10)
to this
strCommentOld = .Comment.Text & Chr(10) & Application.UserName & Chr(10) & Chr(10)
Hi Tom thank you for your code its really great, I was wondering if you can tweak it a little bit
I’m getting my data from website and I want it to record the changes for each cell in a comment in one column. Any amount of help would be appreciated
Hello, Zayne, thank you.
Can you say which column you want this to be for, and also, is it to be every cell in that column, or maybe not in row 1 if you have a header in there that you do not want to make a comment for if that changes.
Greetings Tom,
Thanks you for the outstanding explanation & help.
Is it possible to do the following. :
Track the changes of a formula-based cell? Example: C1 that contains a formula of =A1&”-“&B1
Where if A1 or B1 changes, C1 updates automatically with a comment showing the changes log
If the aforementioned is not feasible, how can I apply the following:
1- To have different comment layouts for columns A and B. Example: A displays date while B doesn’t.
2-To move & combine both comments of A1 & B1 and display them in C1. Example: A1 comments is: 11/29/21 Tom.
B1 comments is 100.
Desired Result:
Comment will only be shown in C1 as follows: 11/29/21 Tom100
Note: no comments to be shown in either A or B.
I appreciate your help
Yes, it is possible to track the changes of a formula based cell, by using the Calculate event if you want to monitor cell C1, or the Change event if you want to monitor either cell A1 or cell B1.
In my opinion, your other idea of different comment layouts is more complicated than it needs to be, so I would use either of the two aforementioned events and follow the code in my example. If you get stuck, post back with what you tried that did not work.
If I copy and paste a cell from another cell, is there a way that the code can add that as a change?
This is probably more of what you would use.
