Thanks Gord. Would the user need to run the macro each time to activate the
formatting? or will it automatically update it when the user enters "dept"
into a cell in that range?

"Gord Dibben" wrote:

CF cannot do this for.

You would need VBA

Right-click on the Sheet tab and "View Code". Copy/paste this code into
that module.

Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Variant, i As Integer
If Not Intersect(Target, Columns("B")) Is Nothing Then
If Target.HasFormula = False Then
v = Target.Value
Application.EnableEvents = False
For i = 1 To Len(v)
If (Mid(v, i, 4)) = "Dept" Then
Target.Characters(Start:=i, _
Length:=4).Font.ColorIndex = 2
End If
Next i
Application.EnableEvents = True
End If
End If
End Sub


Gord Dibben MS Excel MVP

On Wed, 10 Feb 2010 07:27:02 -0800, Roady
wrote:

Hi -
I know how to get a cell to change color based on a value or partial value
but the problem is that I don't want the entire cell to change formatting -
only 1 word. Anytime the word "Dept" occurs in Col B, I want it to be
formatted in white text so as to appear invisible to users. Problem is that
it will always occur before a # such as Dept22 or Dept85. I want the # to
remain visible while the word Dept is in white text.

any way to do this?

thanks!!

.