Conditional Formatting - only 1 word in the cell changes color?

  • Thread starter Thread starter Roady
  • Start date Start date
R

Roady

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!!
 
Yes, unfortunately, I have to include Dept in the same cell b/c it is a
"named cell" so that the column after it can have a dependant drop down based
 
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
 
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?
 
It is event code and hides the word "dept" or "Dept" whenever a string like
abcdept123 is entered in any cell of column B.

I'm surprised you did not see this after you copied the code to the sheet.


Gord
 
Hi Gord - do I need to take your VBA and modify certain pieces of it to make
it work with my particular sheet? I hope that's not a stupid question! :)

It is highlighting the line containing For i = 1 To Len(v) with an error.
Thanks!
 
Tested in both 2003 and 2007

You could try Dim V as String but works for me as Variant or String

If you want, send me your workbook via email

gorddibbATshawDOTca change the obvious.


Gord
 
Back
Top