VBA to change the color of a word if it appears.

handysmurf

Board Regular
Joined
Jan 17, 2015
Messages
97
Office Version
  1. 365
Platform
  1. Windows
just the word (or string) itself, not the background and not all the words in the cell. This would be based on a list of words that appear in column FD. In some cases it would be several words together such as "head up your ***". Most are just one word. Its fine if it just works on the entire sheet and I want them to be bright red FF000 and bold. Also, in one case, what I'm looking for is "pos" ... which appears in many words but I only want it to highlight if it stands alone.

I've found some similar threads but I don't know enough about VBA to make the changes necessary to do what I need.

Thanks in advance
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
VBA Code:
Sub RedBold(rng, start As Long, length As Long)
    With rng.Characters(start:=start, length:=length).Font
        .FontStyle = "Bold"
        .Color = -16776961
    End With
End Sub

Sub HighlightKeyWords()
    'set Const CaseSensitive to true for case sensitive highlighting
    Const CaseSensitive = False
    
    'dimension variables
    Dim WordList, c, w, Punctuation, p, cc As String, ww As String
    Dim i As Long, start As Long, length As Long, ctr As Long, cols As Long
    
    'intitialize punctuation symbols to ignore in an array
    'if any other special symbols cause problems then add them to this array
    Punctuation = Array(".", ",", "-", ";", ":", "?", "!")
    
    'Check that named Range WordList exists and is a single column
    On Error Resume Next
    cols = Range("WordList").Columns.Count
    If Err = 1004 Then
        MsgBox "Establish a single column named range WordList before running this Sub"
        Exit Sub
    Else
        If cols <> 1 Then
            MsgBox "named range WordList must be a single column"
            Exit Sub
        End If
    End If
    Err.Clear
    On Error GoTo 0
    
    'initialize search word list, based on named range WordList
    'consisting of a single column of words or phrases
    WordList = Range("WordList").Value2
    For Each w In WordList
        i = i + 1
        For Each p In Punctuation
            w = Replace(w, p, " ")
        Next p
        WordList(i, 1) = " " & w & " "
        If Not CaseSensitive Then WordList(i, 1) = LCase(WordList(i, 1))
    Next w
    
    'go through each cell in the selected range and highlight in red/bold the key words/phrases
    For Each c In Selection
        cc = c.Value2
        For Each p In Punctuation
            cc = Replace(cc, p, " ")
        Next p
        cc = " " & cc & " "
        If Not CaseSensitive Then cc = LCase(cc)
        For Each w In WordList
            start = 1
            length = 0
            Do While start <> 0
                start = InStr(start + length, cc, w)
                length = Len(w) - 1
                If start > 0 Then RedBold c, start - 1, length
            Loop
        Next w
    Next c
        
End Sub

Highlight Key Words.xlsm
ABCD
1WordListI ate an apple and an orange yesterday
2appleone banana two banana three banana four
3pearthey went bananas
4apple and a pearI speared some scrapple and had an orange
5orangeI ate an apple, an orange, and a banana.
6bananaI'll eat an apple and a pear later
7How about an APPLE???!!!
8
Sheet2


1716657416442.png
 
Upvote 0
ok I put the code in and names the column FD "wordlist". It isn't working?

Sorry ... I'm sure it's something I did or didn't do.
 
Upvote 0
Only name the range of cells with words in them WordList not the entire 1000000+ cell column.

To run the code you need to select the cells you want to highlight and then execute the code. You can put a button on the sheet and assign it to the macro, or assign it to a shortcut key, or run it from the ribbon - Developer, Macro, select HighlightKeyWords and Run.
 
Upvote 0
ok it highlights the word in range "wordlist" red ...but no where else. So progress. Not sure it matters but I also added a word and adjusted the range ... but the added word is not highlighted. Do I have to run this or does it just work automatically?
 
Upvote 0
How do you mean?
Say, you want to highlight "car", which words below should be highlighted?
car
cars
card
... so if is "you are really a pos" I just want "pos" highlighted. I don't want the words position, supposed, posted, post .... a thousand other words that have pos in them .... highlighted
 
Upvote 0
I don't want the words position, supposed, posted, post .... a thousand other words that have pos in them .... highlighted
So, in my example, all three words should be highlighted?
My point is car & card have different meaning.
 
Upvote 0

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top