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

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

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

What else is likely to appear in column B? I'm just thinking that perhaps
separating the "Dept" and the numeral in adjoining cells may be an option.
--
Russell Dawson
Excel Student


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

Conditional Formatting - only 1 word in the cell changes color
 

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
on it. I can't name the cell with just the # because Excel won't let you
start a named cell with a #.

"Russell Dawson" wrote:

What else is likely to appear in column B? I'm just thinking that perhaps
separating the "Dept" and the numeral in adjoining cells may be an option.
--
Russell Dawson
Excel Student


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

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

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

Conditional Formatting - only 1 word in the cell changes color
 

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

.

Conditional Formatting - only 1 word in the cell changes color
 

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


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

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

.

Conditional Formatting - only 1 word in the cell changes color
 

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" wrote:

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


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

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

.


.

Conditional Formatting - only 1 word in the cell changes color
 

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


On Thu, 11 Feb 2010 07:31:02 -0800, Roady
wrote:

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" wrote:

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


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

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

.


.