VBA - add comment to cell that script highlights

[mike760534211]

I have a code that highlights the cell if the cell value matches the array and it is working perfectly.
I was wanting it to also add a comment to the cell it locates from the array and after much searching for this I am unable to get it to add the comment correctly to the cell it found from the array.
Any assistance would be greatly appreciated.

Sub Highlight_Cell_frm_Array()

Dim vntWords As Variant
Dim lngIndex As Long
Dim rngFind As Range
Dim strFirstAddress As String
Dim lngPos As Long


vntWords = Array("array criteria here")

With ActiveSheet.UsedRange
    For lngIndex = LBound(vntWords) To UBound(vntWords)
        Set rngFind = .Find(vntWords(lngIndex), LookIn:=xlValues, LookAt:=xlPart)
        If Not rngFind Is Nothing Then
            strFirstAddress = rngFind.Address
            Do
                lngPos = 0
                Do
                    lngPos = InStr(lngPos + 1, rngFind.Value, vntWords(lngIndex), vbTextCompare)
                    If lngPos > 0 Then
                        With rngFind.Characters(lngPos, Len(vntWords(lngIndex)))
                            .Font.Bold = True
                            .Font.Size = .Font.Size + 2
                            .Font.ColorIndex = 3
                        End With
                    End If
                Loop While lngPos > 0
                Set rngFind = .FindNext(rngFind)
            Loop While rngFind.Address <> strFirstAddress
        End If
    Next
End With
End Sub

 

[Snakehips]

Mike, try...

Sub Highlight_Cell_frm_Array()

Dim vntWords As Variant
Dim lngIndex As Long
Dim rngFind As Range
Dim strFirstAddress As String
Dim lngPos As Long




vntWords = Array("array criteria here")


With ActiveSheet.UsedRange
    For lngIndex = LBound(vntWords) To UBound(vntWords)
        Set rngFind = .Find(vntWords(lngIndex), LookIn:=xlValues, LookAt:=xlPart)
        If Not rngFind Is Nothing Then
[COLOR=#ff0000]        rngFind.AddComment ("Add Your Comment Here!")[/COLOR]
        
            strFirstAddress = rngFind.Address
            Do
                lngPos = 0
                Do
                    lngPos = InStr(lngPos + 1, rngFind.Value, vntWords(lngIndex), vbTextCompare)
                    If lngPos > 0 Then
                        With rngFind.Characters(lngPos, Len(vntWords(lngIndex)))
                            .Font.Bold = True
                            .Font.Size = .Font.Size + 2
                            .Font.ColorIndex = 3
                        End With
                    End If
                Loop While lngPos > 0
                Set rngFind = .FindNext(rngFind)
            Loop While rngFind.Address <> strFirstAddress
        End If
    Next
End With
End Sub

Hope that helps.

 

[mike760534211]

worked perfectly thank you