Different font colors in one cell

[Leslie]

Does anyone know a function that can change the color of a
word within a cell containing text? If not a function, a
number format or conditional format? Like I can highlight
the word "quick" in "the quick brown fox" in red bold font
while the rest is black?

[Jim Rech]

>>Like I can highlight the word "quick" in "the quick brown fox" in red bold
font while the rest is black?

Do exactly that. After highlighting the word open the Format, cells dialog.

--
Jim Rech
Excel MVP

[CLR]

Left-click on the cell you want to modify, then go up to the window next to
the = sign above the column letters and highlight the characters you wish
to change the format of, then Right-click > Format Cells and set up
whatever format changes you want.......

Vaya con Dios,
Chuck, CABGx3

"Leslie" <leslie_...@yahoo.com> wrote in message
news:400f01c324f9$c143f9a0$a301...@phx.gbl...

[John C. Harvey]

>.
>Use your mouse to highlight the word or letter that you
want to change in the cell. Then select color, font type,
bold, or what-ever from your toolbar above

[Leslie]

Thanks, all. I already know how to do it with my mouse
and keyboard... =) What I need is a formula or a number
format, because the contents of the cell may change, and I
want to apply it to a range. And no, I do not want to use
Find and Replace either (which doesn't work, I think,
because it applies to all the contents of the cell and not
just the word I want changed). Is this even possible
without having to write a macro?

[Dave Peterson]

If the cell contains a formula, then you can't apply this type of format--same
with real numbers (not Text numbers!).

But maybe you could convert to values (if you have to) and run a macro:

Modify your list of words in the "mywords = array(...)" line.

Also change this to match what you want:

.Name = "Arial"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = True
.Superscript = False
.Subscript = True
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3

(Delete/comment out the lines you don't want to change. I'd record a macro that
set the font to get all the names/colors correct.)

Then select your range and run the macro.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Option Explicit
Sub testme()

Application.ScreenUpdating = False

Dim myWords As Variant
Dim myRng As Range
Dim foundCell As Range
Dim iCtr As Long
Dim FirstAddress As String
Dim AllFoundCells As Range
Dim myCell As Range
Dim myStartPos As Long
Dim myWordLen As Long

myWords = Array("quick", "fox", "lazy", "dog")

Set myRng = Selection

On Error Resume Next
Set myRng = Intersect(myRng, _
myRng.Cells.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please choose a range that contains text constants!"
Exit Sub
End If


For iCtr = LBound(myWords) To UBound(myWords)
FirstAddress = ""
Set foundCell = Nothing
With myRng
Set foundCell = .Find(what:=myWords(iCtr), _
LookIn:=xlValues, lookat:=xlPart, _
after:=.Cells(.Cells.Count))

If foundCell Is Nothing Then
MsgBox myWords(iCtr) & " wasn't found!"
Else
Set AllFoundCells = foundCell
FirstAddress = foundCell.Address
Do
If AllFoundCells Is Nothing Then
Set AllFoundCells = foundCell
Else
Set AllFoundCells = Union(foundCell, AllFoundCells)
End If
Set foundCell = .FindNext(foundCell)

Loop While Not foundCell Is Nothing _
And foundCell.Address <> FirstAddress
End If

End With

If AllFoundCells Is Nothing Then
'do nothing
Else
For Each myCell In AllFoundCells.Cells
myStartPos = 1
Do While myStartPos > 0
myWordLen = Len(myWords(iCtr))
myStartPos = InStr(myStartPos, myCell.Value, _
myWords(iCtr), vbTextCompare)
If myStartPos > 0 Then
With myCell.Characters(Start:=myStartPos, _
Length:=myWordLen).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = True
.Superscript = False
.Subscript = True
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
End With
myStartPos = myStartPos + myWordLen
End If
Loop
Next myCell
End If
Next iCtr

Application.ScreenUpdating = True

End Sub

--

Dave Peterson
ec3...@msn.com