Home » Blogs » Vishesh's blog

Customised Cell Comments

Vishesh's picture
Submitted by Vishesh on 5 February, 2011 - 15:37
This is a small piece of code showing how to customise your cell comments to make it look better. The code works fine in Excel 2003. Some R&D is required to find out why font color applied in Excel 2007 is not correct. To make it work in Excel 2007 use ColorIndex instead of Color. 
Sub ModifyCellComment()
 
    Dim cmt     As Comment
 
    Dim shp     As Shape
 
 
 
    Set cmt = Range("rngModified").Comment
 
    Set shp = cmt.Shape
 
 
 
    With shp
 
        With .TextFrame.Characters.Font
 
            .Size = Range("rngFormat").Font.Size
 
            .Bold = Range("rngFormat").Font.Bold
 
            .Name = Range("rngFormat").Font.Name
 
            .Color = Range("rngFormat").Font.Color
 
        End With
 
        .Fill.ForeColor.RGB = Range("rngFormat").Interior.Color
 
        'Color of first character of comment

        .TextFrame.Characters(1, 1).Font.Color = Range("rngFirstChar").Interior.Color
 
        .Fill.Solid
 
    End With
 
 
 
    Set cmt = Nothing
 
    Set shp = Nothing
 
End Sub

 

Customised Cell Comments

AttachmentSize
Comments.xls40.5 KB
»