Need help with Auto Comment macro

[davidw]

I have a macro that is worksheet activated. It works great and has for a long time now, however whenever i edit the comment with BOLD or color it reverts back to standard text the next time the comment is activated. Ive attached the file. Could someone please help. Thank you.

[davidw]

Can anyone help?

[Paul_Hossler]

I'm not sure about all your logic, but it seems that your

[VBA]
OldComment = .Comment.Text
.ClearComments ' <------
[/VBA]

is also clearing the formating

Maybe ...

[VBA]
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

'copy previous value to another sheet
'create another worksheet to put data on and hide it
Sheet2.Range(Target.Address) = Target.Text

Const COMMENT_HEAD As String = "ITEM HISTORY BELOW"
'====================================
Dim strDate As String
Dim strTime As String
Dim strOldComment As String

strDate = date
strTime = Time
'====================================
'//clearing more than one cell causes an error which will allow you to delete

On Error Resume Next
'//(it will not overwrite an existing comment but will add after)
With Target

strOldComment = .Comment.Text

If Len(strOldComment) = 0 Then
.AddComment
.Comment.Visible = False
.Comment.Text Text:=COMMENT_HEAD & vbNewLine _
& Sheet2.Range(.Address) & " " & strDate & " " & Target.EntireRow.Range("b1")
Else
.Comment.Text Text:=strOldComment & vbNewLine _
& Sheet2.Range(.Address) & " " & strDate & " " & Target.EntireRow.Range("b1")
End If

End With
Application.Run "'03-27-09 comment program to track changes.xls'!Comments_AutoSize"
End Sub
[/VBA]

Paul

[davidw]

That didnt help. It appears that the 1st text in the comment determines what they all will be. Example: If I change the first letter in the comment to Bold Blue then when it regenerates it makes everything bold blue.