VBA - add  comment to cell that script highlights
Results 1 to 3 of 3

Thread: VBA - add comment to cell that script highlights

  1. #1
    New Member
    Join Date
    Sep 2014
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question VBA - add comment to cell that script highlights

    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.

    Code:
    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

  2. #2
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,657
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA - add comment to cell that script highlights

    Mike, try...

    Code:
    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
            rngFind.AddComment ("Add Your Comment Here!")
            
                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.
    Last edited by Snakehips; Jul 19th, 2019 at 11:52 AM.
    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  3. #3
    New Member
    Join Date
    Sep 2014
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - add comment to cell that script highlights

    worked perfectly thank you

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •