Multiple Formats per Cell

[Timo]

I'm trying to search for specific words in a MsExcel spreadsheet and format
them (using either a macro or formula) with multiple font colors

(e.g. search for "MY NAME IS TIM", and format it so the word "NAME" would be
BOLD RED, and all the other text would be black.

I'm basically trying to highlight individual words only, but not the entire
text string or the entire cell.

Is there a way to do this with either a formula or macro?

 

[galimi]

A function only returns a value, you will need a macro for this.
You can tie the macro to a change event so it behaves more like a function.

 

[Gord Dibben]

Timo

Sub color_words()
Dim Cell As Range, tempR As Range, mystring As String, _
myword As String, rng As Range
mystring = InputBox("Enter the search string")
If mystring = "" Then Exit Sub
For Each Cell In ActiveSheet.UsedRange
If Cell.Value = UCase(mystring) Then
If tempR Is Nothing Then
Set tempR = Cell
Else
Set tempR = Union(tempR, Cell)
End If
End If
Next Cell
If tempR Is Nothing Then
End
End If
tempR.Select
myword = InputBox("Enter the word to format ")
If myword = "" Then Exit Sub
Mylen = Len(myword)
Set rng = Selection
rng.Cells.Font.ColorIndex = 0
For Each Cell In rng
start_str = InStr(Cell.Value, UCase(myword))
If start_str Then
Cell.Characters(start_str, Mylen).Font.ColorIndex = 3
End If
Next Cell
End Sub


Gord Dibben MS Excel MVP

 

[Timo]

This is a good start. Thanks! However, the only problem I have with this
sub is that it prompts me for the text string. I'm searching through several
hundred different text strings and trying to highlight specific words if they
appear in a specific column (within an entire workbook, not just one
worksheet).
Any further advice?

 

[Gord Dibben]

So the text string doesn't matter?

Just the single word in any text string is what you're looking for?

Sub Highlight_Word()
Dim rng As Range
Dim ws As Worksheet
Dim Cell As Range
Dim myword As String
Dim start_str As Integer
Dim Mylen As Integer
Dim N As Single
myword = InputBox("Enter the word ")
If myword = "" Then Exit Sub
Mylen = Len(myword)
For Each ws In ActiveWorkbook.Worksheets
ws.Select
With Selection
Set rng = Range(Range("A1"), Cells(Rows.Count, 1).End(xlUp))
For Each Cell In rng
Cell.Font.ColorIndex = 0
start_str = InStr(Cell.Value, myword)
If start_str Then
Cell.Characters(start_str, Mylen).Font.ColorIndex = 3
End If
Next Cell
End With
Next ws
End Sub


Gord

 

[Timo]

Close enough. I was able to modify your suggested macro to fit my needs. I
actually took out the InputBox so the macro would automatically check for the
words, and had to eliminate the code resetting the font color to 0, so I use
it to search for and modify several different words.

Thank you!!!!

 

[Gord Dibben]

Thanks.

Curious though. How does Excel and the macro know which word(s) to look for
if you removed the inputbox?


Gord Dibben MS Excel MVP