+ Reply to Thread
Results 1 to 2 of 2

Highlighting a word or phrase

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    08-03-2009
    Location
    Cary, NC
    MS-Off Ver
    Excel 2016
    Posts
    109

    Highlighting a word or phrase

    I have the following code which works:

    Sub Extractor()
    
        'Change this to the correct column letter that will be searched for keywords/strings
        Const strKeywordCol As String = "AT"
        
        Dim wsData As Worksheet
        Dim wsKywrds As Worksheet
        Dim rngKywrds As Range
        Dim DataCell As Range
        Dim rngDel As Range
        
        'Change these to the correct sheet names
        Set wsKywrds = Sheets("Keywords")
        Set wsData = Sheets("Clarify Data")
        
        'Make sure the keywords/strings are in column A starting in cell A1
        Set rngKywrds = wsKywrds.Range("A1", wsKywrds.Cells(Rows.Count, "A").End(xlUp))
            
        For Each DataCell In Intersect(wsData.UsedRange, wsData.Columns(strKeywordCol)).Cells
            'Check if this cell contains any of the keywords/strings
            If Evaluate("Sumproduct(--(IsNumber(Search(" & rngKywrds.Address(External:=True) & "," & DataCell.Address(External:=True) & "))))=0") Then
                'This cell does NOT contain any of the keywords/strings, add this cell to rngDel
                Select Case rngDel Is Nothing
                    Case True:  Set rngDel = DataCell
                    Case Else:  Set rngDel = Union(rngDel, DataCell)
                End Select
            End If
        Next DataCell
        
        'Delete all rows in rngDel
        If Not rngDel Is Nothing Then rngDel.EntireRow.Delete
        
        Set wsData = Nothing
        Set wsKywrds = Nothing
        Set rngKywrds = Nothing
        Set rngDel = Nothing
        
    End Sub
    I need the code to also highlight the keyword or phrase that it found so that when I look at the cell later it will stand out.

  2. #2
    Forum Contributor
    Join Date
    07-27-2012
    Location
    California, USA
    MS-Off Ver
    Excel 2003
    Posts
    198

    Re: Highlighting a word or phrase

    Here is example code that highlights specific words or phrases in a column of values.

    In my file, I listed the keywords or phrases in column A starting in cell A1 while the
    text to be searched and partially formatted in bold font resided in column B, starting
    in cell B1.

    Sub Highlight_Keywords()
    'apply bold font to all found keywords or phrases, including multiple targets
    'found in the same string of text, for whole words only.
    'macro ignores case and does not format keywords if word is punctuated or is
    'preceeded or followed by other characters such as a hyphen or parenthesis.
    
    Dim i As Integer, pos As Integer, intLength As Integer
    Dim lastDataRow As Long, keyWordCount As Long, j As Long
    Dim keyWord As String
    
        Application.ScreenUpdating = False
        
        With Sheets("Sheet1")
            keyWordCount = .Range("A65536").End(xlUp).Row
            lastDataRow = .Range("B65536").End(xlUp).Row
       
            For i = 1 To keyWordCount
                'adding a leading and trailing space will locate whole keywords at begining or end of text
                keyWord = " " & UCase(Trim(.Cells(i, 1).Value)) & " "
                intLength = Len(Trim(keyWord))
                For j = 1 To lastDataRow
                    pos = InStr((pos + 1), " " & UCase(.Cells(j, 2).Value) & " ", keyWord)
                    While pos > 0
                        If pos = 0 Then Exit For
                        .Cells(j, 2).Select
                        ActiveCell.Characters(Start:=pos, Length:=intLength).Font.FontStyle = "Bold"
                        pos = InStr(pos + Len(keyWord), " " & UCase(.Cells(j, 2).Value) & " ", keyWord)
                    Wend
                Next
            Next
            .Range("A1").Select
        End With
    
        Application.ScreenUpdating = True
        
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1