comment text property.

[SIXTH SENSE]

hi gurus!!!

Is there a way to change the font size and style of a comment of a certain cell?
I mean is it possible to have the sizes of comments varies in one worksheet?

thanks!!!

 

[mgade]

What if you ran the highlight code on some sort of worksheet event?

Like when a certain cell recalculated? I did not know the possibillity existed. How is that done?

 

[PA HS Teacher]

I don't know if its possible to have an event triggered only if a particular cell calculates. But if the sheet calculates you could run something like the following.

Private Sub Worksheet_Calculate()
' Call Highlight Routine
End Sub

Though this could get slow, if you have many cells to run this routine for each time a change is made.


To run the routine whenever a certain cell is selected you could do the following.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("A1").Address Then
' Call Highlight Routine Here
End Sub

I know you can have a worksheet change event.
I don't think (but I don't know) that a recalculation of a cell constitutes a change for that cell, and I don't have time to play around with it right now.'

In any case, play around with events.

 

[mgade]

Thanks for the idea, I will try it out!

 

[CA_User]

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

This works really well for me too. But how do I modify if I want to bold the work VBA as well? In this case, just Excel and VBA should be bolded and all else is unbolded.

Thanks!!!