VBA-Bold font in comments box

[Kenn]

Hi.
I am currently using a formula to populate a cell comment box based on a look-up results of the box contents.

Option Explicit
Function AddCommentFromVlookup(myCommentCell As Range, _
                               myTable As Range, _
                               myColumn As Long, _
                               myBoolean As Boolean) As Variant
    Application.Volatile True
    Dim res As Variant 'could be an error
    Dim myLookupCell As Range
    Dim myStr As String
    With myCommentCell
        res = Application.Match(.Value, myTable.Columns(1), myBoolean)
        If IsError(res) Then
            myStr = "Not found"
        Else
            Set myLookupCell = myTable.Columns(myColumn).Cells(1)(res)
            myStr = myLookupCell.Text
        End If
        If .Comment Is Nothing Then
            'do nothing
        Else
            .Comment.Delete
        End If
        .AddComment Text:=myStr
    End With
    AddCommentFromVlookup = "" 'the value in the cell with the function
    
    'USE THIS CODE TO ACTIVATE THE FORMULA
        '=AddCommentFromVlookup(A1,A:B,2,FALSE)
End Function

Once this is done I am then running a VBA script to format the comment box.

Sub ChgAllComments()
    Dim Cell As Range
        Dim UserN As String
        Dim commt As String
        Dim myBolds
        Dim i As Long
        
        
    For Each Cell In Cells.SpecialCells(xlCellTypeComments)
        With Cell.Comment.Shape.TextFrame
            .Characters.Font.Name = "Tahoma"
            .Characters.Font.Size = 8
            .Characters.Font.FontStyle = "Regular"
            .AutoSize = True            
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .ReadingOrder = xlContext
        
        End With
Application.DisplayCommentIndicator = xlCommentIndicatorOnly

    Next
End Sub

Problem i have is that I need to format first 4 lines of the comment box to be bold (this will always be 4 lines in all comment boxes)

Any suggestions would be greatly appreciated.
Kenn

 

[sericom]

Try this

Sub ChgAllComments()
    Application.ScreenUpdating = False
    Dim Cell As Range
    Dim UserN As String
    Dim commt As String
    Dim myBolds
    Dim i As Long
        
    For Each Cell In Cells.SpecialCells(xlCellTypeComments)
        pos = 1
        For i = 1 To 4
            nl = InStr(pos, Cell.Comment.Text, Chr(10))
            If nl = 0 Then
                nl = Len(Cell.Comment.Text)
                Exit For
            End If
            pos = nl + 1
        Next
        With Cell.Comment.Shape.TextFrame
            .Characters.Font.Name = "Tahoma"
            .Characters.Font.Size = 8
            .Characters.Font.FontStyle = "Regular"
            .AutoSize = True
            .Characters(1, nl).Font.Bold = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .ReadingOrder = xlContext
        End With
        Application.DisplayCommentIndicator = xlCommentIndicatorOnly
    Next
    Application.ScreenUpdating = True
End Sub

 

[Kenn]

Thanks Sericom. That was perfect.
Kenn