Coloring certain words in strings based on list....?

Hi

I have a column B with a lot of text strings (of varying length) beginning in B2. In the first row C1 to "??"1, I have a list of words or "word*", that I would like to give a color in the text strings. Only these words should get colors, not the whole cell. As a start they could just get the same color, at some point, though, it would be very cool if they could also get different colors..

Is this at all possible?

Jacob

Based on code from this thread:http://www.excelforum.com/excel-gene...html?p=2963260

Wow, cool...
One thing, though. Is it possible to let the macro use wild cards?
To stay within the example from the sheet you attached:

Cat is colored red, but what if the keyword mouse was mou* instead, and alle words containing mou should be green, not just mouse.

.........double post, sorry.............

I suppose the Regex part of the macro would have to be re-written, but that's beyond my skills.

Replace the entire code with

Option Explicit
Option Compare Text

Sub test()
    Dim myPtn As String, r As Range, m As Object, dic As Object, e
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    Columns(2).Font.ColorIndex = xlAutomatic
    For Each r In Range("c1", Cells(1, Columns.Count).End(xlToLeft))
        dic(r.Value) = r.Interior.Color
    Next
    With CreateObject("VBScript.RegExp")
        .Global = True
        .IgnoreCase = True
        .Pattern = "(\*)"
        myPtn = "\b(" & .Replace(Join$(dic.keys, "|"), "\w$1") & ")\b"
        .Pattern = myPtn
        For Each r In Range("b2", Range("b" & Rows.Count).End(xlUp))
            If .test(r.Value) Then
                For Each m In .Execute(r.Value)
                    For Each e In dic
                        If m.Value Like e Then Exit For
                    Next
                    r.Characters(m.firstindex + 1, m.Length).Font.Color = dic(e)
                Next
            End If
        Next
    End With
End Sub

Hi jindon, WHER or anyone else

just a follow up question. Say I would like to not only color the word, but also make it Bold, where should the code be changed?

J

change

r.Characters(m.firstindex + 1, m.Length).Font.Color = dic(e)

to

with r.Characters(m.firstindex + 1, m.Length).Font
    .Color = dic(e)
    .bold = true
end with

Thank you..!