FORMAT COMMENT TEXT WITH MACRO

[Sunil Patel]

i need to enter as a comment two words with the "Enter key" press between
the words. This i can do
But i want to format the text in the comment box. i.e top word in Arial 14
bold in blue and the bottom word in Arial 14 bold in pink.
is this possible - Code would be a great help if possible

Sunil

 

[Tom Ogilvy]

Turn on the macro recorder and do it manually. Should give you what you
need.

 

[Tom Ogilvy]

Pantel informed me it didn't record and he is correct.

Sub AddCustomComment()
On Error Resume Next
ActiveCell.Comment.Delete
On Error GoTo 0
Set cmt = ActiveCell.AddComment( _
Text:="ABCD" & Chr(10) & "EFHG")
With cmt.Shape.TextFrame.Characters(1, 4)
.Font.Name = "Arial"
.Font.Bold = True
.Font.ColorIndex = 5
End With
With cmt.Shape.TextFrame.Characters(6, 4)
.Font.Name = "Arial"
.Font.Bold = True
.Font.ColorIndex = 7
End With
End Sub

 

[Tom Ogilvy]

Right you are:

Sub AddCustomComment()
On Error Resume Next
ActiveCell.Comment.Delete
On Error GoTo 0
Set cmt = ActiveCell.AddComment( _
Text:="ABCD" & Chr(10) & "EFHG")
With cmt.Shape.TextFrame.Characters(1, 4)
.Font.Name = "Arial"
.Font.Bold = True
.Font.ColorIndex = 5
End With
With cmt.Shape.TextFrame.Characters(6, 4)
.Font.Name = "Arial"
.Font.Bold = True
.Font.ColorIndex = 7
End With
End Sub

Your interpretation of pink may be different from mine, so adjust the last
colorIndex value from 7 to your definition.

Adjust the numbers in Characters to reflect the length of the words you add
in the comment.

 

[Tom Ogilvy]

Left out the size 14

Sub AddCustomComment()
On Error Resume Next
ActiveCell.Comment.Delete
On Error GoTo 0
Set cmt = ActiveCell.AddComment( _
Text:="ABCD" & Chr(10) & "EFHG")
With cmt.Shape.TextFrame.Characters(1, 4)
.Font.Name = "Arial"
.Font.Size = 14
.Font.Bold = True
.Font.ColorIndex = 5
End With
With cmt.Shape.TextFrame.Characters(6, 4)
.Font.Name = "Arial"
.Font.Size = 14
.Font.Bold = True
.Font.ColorIndex = 7
End With
End Sub