Excel Forum

Code: Select all

Sub comment_Item()
     Dim rAll As Range, r As Range
    Dim rhAll As Range, rh As Range
    Dim strCmt As String, found As Boolean
    Dim xComment As Comment
    
    With Sheets("Sheet1")
        Set rAll = .Range("L7", .Range("L" & .Rows.Count).End(xlUp))
        For Each r In rAll
            If r.Value >= 0 Then
                found = False
                With .Parent.Sheets("sheet1")
                    Set rhAll = .Range("C7:C700")
                    For Each rh In rhAll
                        If rh.Value = r.Offset(0, -9).Value Then
                            strCmt = .Cells(rh.Row, 2)
                            found = True
                            Exit For
                        End If
                    Next rh
                End With
                If found Then
                    If r.Comment Is Nothing Then
                        r.AddComment
                        r.Comment.Shape.TextFrame.AutoSize = True
                        
                    End If
                    r.Comment.Text strCmt
                     r.Comment.Shape.TextFrame.AutoSize = True
                End If
            Else
                If Not r.Comment Is Nothing Then
                    r.ClearComments
                End If
            End If
             
        Next r
    End With
End Sub