Appending (or prepending) to a comment, keeping comment format

[BenW71]

I have a workbook where i am comparing two worksheets that should be identical for the most part, including comments. When there is a difference, I am marking the cell yellow in the main sheet and then creating a formatted comment with details on what is different.

As many of the cells have formatted comments already, I have created a function that inserts a new comment at the end of an existing comment and keeps the formatting of both comments.

Below is the code for what i have. This would be called after i compare the comment text of the two fields and determine they are different.

The code seems to work fine. However copying one character at a time can't be very efficient. I would be able to insert the comment with something like TF.Characters(TF.Characters.Count+1).Insert (DiffR.Comment.text). But how can i copy over the font structure for the bold and size formatting of the comment with a single statement using SOMETHING LIKE TF.Characters(start position before the copy,start position before the copy + copytf.characters.count).Font = CopyTF(0,copytf.characters.count).font? This doesn't seem to work...

Public Sub AddDifferentComment(R As Range, DiffR As Range)
Dim TF As TextFrame, CopyTF As TextFrame, theChar As String
Dim SeparatorStr As String
Dim i As Integer


    SeparatorStr = Chr(10) & "---------------------------" & Chr(10)
    
        
    Set TF = R.Comment.Shape.TextFrame
    Set CopyTF = DiffR.Comment.Shape.TextFrame
    
    TF.Characters(TF.Characters.Count).Insert (SeparatorStr)
    
    For i = 1 To CopyTF.Characters.Count
        theChar = CopyTF.Characters(i, 1).text
        TF.Characters(TF.Characters.Count + 1).Insert (theChar)
        TF.Characters(TF.Characters.Count).Font.Bold = CopyTF.Characters(i, 1).Font.Bold
        TF.Characters(TF.Characters.Count).Font.Size = CopyTF.Characters(i, 1).Font.Size
    Next i
End Sub

 

[Norie]

Have you tried adding the text for the new comment and then applying the formatting?

 

[BenW71]

Yes i have tried without success. That's what i was alluding to when i was looking for SOMETHING LIKE TF.Characters(start position before the copy,start position before the copy + copytf.characters.count).Font = CopyTF(0,copytf.characters.count).font? This doesn't seem to work...

I don' tknow how to apply a formatting for a range of characters in the existing textframe structure.

 

[BenW71]

Distilling this to its most basic question... If i have 2 textframe structures from 2 different ranges' comments, how do i create a new comment with one concatenated before the other retaining the formatting... This way i could use that sub for prepending comments, adding 2-3 comments in there, etc.

 

[BenW71]

Bouncing this thread with hopes for input