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

[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!!

 

[Roady]

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

 

[Gord Dibben]

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

 

[Roady]

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]

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

 

[Roady]

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!

 

[Gord Dibben]

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