Font color

[-]

I have an excel file that has about 30,000 cells containing names.

How can I quickly change the font color of all the cells depending upon what
word is in the cell.

For example, is there a way to search for the word John amonst the 30,000
cells. Anywhere the word John is found, the font color is changed to red.
Another example would be anywhere the word Bob is found, that word font
color would be changed to green.

Thank you

 

[Pete_UK]

You could use Conditional Formatting, but you would be limited to only
three names being highlighted (unless you have Excel 2007).

Hope this helps.

Pete

 

[Chip Pearson]

You can do something like this with code:

Option Explicit
Option Compare Text

Sub DoColors()
Dim Rng As Range
For Each Rng In ActiveSheet.UsedRange.Cells
Select Case Rng.Text
Case "Joe"
Rng.Font.ColorIndex = 3
Case "John"
Rng.Font.ColorIndex = 4
Case "Mike"
Rng.Font.ColorIndex = 5
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' And so on. Note that you are limited to 56 different colors
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Case Else
Rng.Font.ColorIndex = xlColorIndexAutomatic
End Select
Next Rng
End Sub

Note that you are limited to ColorIndex values between 1 and 56.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[MartinW]

Also have a look here for more options.

http://www.xldynamic.com/source/xld.CFPlus.Download.html

 

[-]

Thank you for the code, I gave it a try, it worked great.

I was wondering if in addition to changing the color for the various names,
is it possible to highlight all the names in bold. Using the example below,
Joe would be bold color 3, John would be bold and in color 4 and Mike would
be bold and in color 5.

The reason I say this is some of the colors are subtle and making them bold
would make them stand out a bit more.

Thank you

 

[Nick Hodge]

You would need to alter each line of

Rng.Font.ColorIndex = 3

to...

With Rng.Font
.ColorIndex=3
.Bold=True
End With

(Note the period before each indented line)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[-]

Thanks Nick, I appreciate the help.

You would need to alter each line of

Rng.Font.ColorIndex = 3

to...

With Rng.Font
.ColorIndex=3
.Bold=True
End With

(Note the period before each indented line)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk