comment text property.

Sure it is, just right click on the cell containing the comment, select Edit Comment, select the comment text (or only a portion of it if you want), then from your formatting toolbar select the font style, size, appearance (bold, italic, or whatever). No need for a macro unless you have a lot of comments to format.

thanks a lot for the reply!!!
but i need it for more than 5000 rows and 15 sheets. maybe i need a vb code to do it. what you think tom?

VBA's got my vote for that many potential comments. What exactly do you want to do, and will the formatting depend on the sheet, or do you want all the comments to be formatted the same (just differently than they currently are)?

ok, here it is.
i would like the comment to be more vissible than the other comments when it is 3 days before a certain deadline. I would like it to look bigger so that is recognized by just passing the mouse over it. if it is overdue ill color it red and return it to normal size when done and change the comment to some notes and change to some other color.

Well, just as an example, seeing as today's date (in most places around the world) is November 17, 2003, and 3 days from now is November 20, 2003, in keeping with your example, this macro would format all comments in the workbook to be 18 point bold courier in red font. Modify the code if your deadline date is stored someplace to avoid the hard-coded aspect of this macro:

Sub Kommentz()
Application.ScreenUpdating = False
Dim x As Comment, y As Worksheet
If Date < #11/20/2003# Then
For Each y In ThisWorkbook.Worksheets
For Each x In y.Comments
With x.Shape.TextFrame.Characters.Font
.ColorIndex = 3
.Bold = True
.Name = "Courier"
.Size = 18
End With
Next x
Next y
End If
Application.ScreenUpdating = True
End Sub

thanks tom.
im working on it. i made some revisions because i dont need it to all the comments. I want it just in B coloumn not in all the comments in the sheet.

So, please add the bold code lines below to Tom's code.


If x.Parent.Column = 2 Then '2 stands for B Column
With x.Shape.TextFrame.Characters.Font
.ColorIndex = 3
.Bold = True
.Name = "Courier"
.Size = 18
End With
End If

Is there a way to format individual words within a comment?

For example,
If a cell comment contained the text, "I like Excel and VBA."

Is there code that could look at the comment in this cell and change the format of a single word. Could it say, make "Excel" red, and leave the rest of the comment text black?

I've tried recording a macro for this process, but nothing about the formatting of an individual word seems to be recorded.

Based on your example, this will format the word "Excel" in bold red font if it is found in the text of any comments on the active sheet. You can modify the term from "Excel" to something else in the code by changing this line
TextToFormat = "Excel"
to some other term.



Sub Test1()
Application.ScreenUpdating = False

Dim cell As Range, i As Integer
Dim cText As String, TextToFormat As String
TextToFormat = "Excel"

On Error Resume Next

For Each cell In Cells.SpecialCells(1)
cText = cell.Comment.Text

If InStr(cText, TextToFormat) > 0 Then
With cell.Comment.Shape.TextFrame
.Characters.Font.Bold = False
.Characters.Font.ColorIndex = 1
For i = 1 To Len(cText) - Len(TextToFormat) Step 1
If Mid(cText, i, Len(TextToFormat)) = TextToFormat Then
.Characters(i, Len(TextToFormat)).Font.Bold = True
.Characters(i, Len(TextToFormat)).Font.ColorIndex = 3
End If
Next i
End With

If Right(cText, Len(TextToFormat)) = TextToFormat Then
With cell.Comment.Shape.TextFrame.Characters((Len(cText) - Len(TextToFormat)), Len(cText)).Font
.ColorIndex = 3
.Bold = True
End With
End If

End If
Next cell

Application.ScreenUpdating = True

If Err.Number <> 0 Then
Err.Clear
Application.ScreenUpdating = True
MsgBox "No cells on this sheet contain comments.", 48, "Nothing to format."
End If

End Sub