Condition Formatting - Work Sheet Event Code
 

I have set up worksheet event code in order to format cells based on text
input.

All works well, but now I would also like the text to match the cell color
in order to hide the text. I have not had any luck getting the font color to
move off black. I am using the following code line: Case "fair":
..Font.ColorIndex = 45

Looking forward to the help!

Danny

Condition Formatting - Work Sheet Event Code
 

Danny, are you certain that the condition is true (can you step through the
code and watch it hit the ".Font.ColorIndex = 45" portion? Can you posst the
surrounding lines of code?

Regards,
Bill



"Danny" wrote:

I have set up worksheet event code in order to format cells based on text
input.

All works well, but now I would also like the text to match the cell color
in order to hide the text. I have not had any luck getting the font color to
move off black. I am using the following code line: Case "fair":
.Font.ColorIndex = 45

Looking forward to the help!

Danny

Condition Formatting - Work Sheet Event Code
 

Do you have any conditional formatting applied to the cells?

--
Regards,
Tom Ogilvy



"Danny" wrote in message
...
I have set up worksheet event code in order to format cells based on text
input.

All works well, but now I would also like the text to match the cell color
in order to hide the text. I have not had any luck getting the font color
to
move off black. I am using the following code line: Case "fair":
.Font.ColorIndex = 45

Looking forward to the help!

Danny

Condition Formatting - Work Sheet Event Code
 

Thanks Bill.
Danny

Here it is:

Const WS_RANGE As String = "B11:b40"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "excellent": .Interior.ColorIndex = 10
Case "good": .Interior.ColorIndex = 5
Case "fair": .Interior.ColorIndex = 45
Case "fair": .Font.ColorIndex = 45

Case "poor": .Interior.ColorIndex = 9
Case "does not exist": .Interior.ColorIndex = 3
Case "": .Interior.ColorIndex = 2

End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub



"Bill Pfister" wrote:

Danny, are you certain that the condition is true (can you step through the
code and watch it hit the ".Font.ColorIndex = 45" portion? Can you posst the
surrounding lines of code?

Regards,
Bill



"Danny" wrote:

I have set up worksheet event code in order to format cells based on text
input.

All works well, but now I would also like the text to match the cell color
in order to hide the text. I have not had any luck getting the font color to
move off black. I am using the following code line: Case "fair":
.Font.ColorIndex = 45

Looking forward to the help!

Danny

Condition Formatting - Work Sheet Event Code
 

Danny,

the reason the font colour doesn't get set is because you have 2 Case "fair"
tests. It will hit the first, take that action (set the interior colour) and
exit, so the second never gets actioned.

What you need to do is to combine both actions in the same case statement

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B11:B40"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "excellent": .Interior.ColorIndex = 10
Case "good": .Interior.ColorIndex = 5
Case "fair":
.Interior.ColorIndex = 45
.Font.ColorIndex = 45
Case "poor": .Interior.ColorIndex = 9
Case "does not exist": .Interior.ColorIndex = 3
Case "": .Interior.ColorIndex = 2
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Danny" wrote in message
...
Thanks Bill.
Danny

Here it is:

Const WS_RANGE As String = "B11:b40"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "excellent": .Interior.ColorIndex = 10
Case "good": .Interior.ColorIndex = 5
Case "fair": .Interior.ColorIndex = 45
Case "fair": .Font.ColorIndex = 45

Case "poor": .Interior.ColorIndex = 9
Case "does not exist": .Interior.ColorIndex = 3
Case "": .Interior.ColorIndex = 2

End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub



"Bill Pfister" wrote:

Danny, are you certain that the condition is true (can you step through
the
code and watch it hit the ".Font.ColorIndex = 45" portion? Can you
posst the
surrounding lines of code?

Regards,
Bill



"Danny" wrote:

I have set up worksheet event code in order to format cells based on
text
input.

All works well, but now I would also like the text to match the cell
color
in order to hide the text. I have not had any luck getting the font
color to
move off black. I am using the following code line: Case "fair":
.Font.ColorIndex = 45

Looking forward to the help!

Danny

Condition Formatting - Work Sheet Event Code
 

BTW, on the Case else, it is better to use

Case "": .Interior.ColorIndex = xlColorIndexNone

otherwise you will lose the cell borders.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Danny" wrote in message
...
Thanks Bill.
Danny

Here it is:

Const WS_RANGE As String = "B11:b40"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "excellent": .Interior.ColorIndex = 10
Case "good": .Interior.ColorIndex = 5
Case "fair": .Interior.ColorIndex = 45
Case "fair": .Font.ColorIndex = 45

Case "poor": .Interior.ColorIndex = 9
Case "does not exist": .Interior.ColorIndex = 3
Case "": .Interior.ColorIndex = 2

End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub



"Bill Pfister" wrote:

Danny, are you certain that the condition is true (can you step through
the
code and watch it hit the ".Font.ColorIndex = 45" portion? Can you
posst the
surrounding lines of code?

Regards,
Bill



"Danny" wrote:

I have set up worksheet event code in order to format cells based on
text
input.

All works well, but now I would also like the text to match the cell
color
in order to hide the text. I have not had any luck getting the font
color to
move off black. I am using the following code line: Case "fair":
.Font.ColorIndex = 45

Looking forward to the help!

Danny

Condition Formatting - Work Sheet Event Code
 

Bill, Bob, Tom,

Thanks for all of the help! Bob your help did the trick, and you are right
as I was wonder how to get my grid lines back if I formatted the cell white.

Bob - My serach prior to posting found one of your posts in another forum
and it was from that post that I educated myself. Thanks!

Danny

"Bob Phillips" wrote:

BTW, on the Case else, it is better to use

Case "": .Interior.ColorIndex = xlColorIndexNone

otherwise you will lose the cell borders.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Danny" wrote in message
...
Thanks Bill.
Danny

Here it is:

Const WS_RANGE As String = "B11:b40"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "excellent": .Interior.ColorIndex = 10
Case "good": .Interior.ColorIndex = 5
Case "fair": .Interior.ColorIndex = 45
Case "fair": .Font.ColorIndex = 45

Case "poor": .Interior.ColorIndex = 9
Case "does not exist": .Interior.ColorIndex = 3
Case "": .Interior.ColorIndex = 2

End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub



"Bill Pfister" wrote:

Danny, are you certain that the condition is true (can you step through
the
code and watch it hit the ".Font.ColorIndex = 45" portion? Can you
posst the
surrounding lines of code?

Regards,
Bill



"Danny" wrote:

I have set up worksheet event code in order to format cells based on
text
input.

All works well, but now I would also like the text to match the cell
color
in order to hide the text. I have not had any luck getting the font
color to
move off black. I am using the following code line: Case "fair":
.Font.ColorIndex = 45

Looking forward to the help!

Danny

Condition Formatting - Work Sheet Event Code
 

I thought I recognised the code <g

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Danny" wrote in message
...
Bill, Bob, Tom,

Thanks for all of the help! Bob your help did the trick, and you are
right
as I was wonder how to get my grid lines back if I formatted the cell
white.

Bob - My serach prior to posting found one of your posts in another forum
and it was from that post that I educated myself. Thanks!

Danny

"Bob Phillips" wrote:

BTW, on the Case else, it is better to use

Case "": .Interior.ColorIndex = xlColorIndexNone

otherwise you will lose the cell borders.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Danny" wrote in message
...
Thanks Bill.
Danny

Here it is:

Const WS_RANGE As String = "B11:b40"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "excellent": .Interior.ColorIndex = 10
Case "good": .Interior.ColorIndex = 5
Case "fair": .Interior.ColorIndex = 45
Case "fair": .Font.ColorIndex = 45

Case "poor": .Interior.ColorIndex = 9
Case "does not exist": .Interior.ColorIndex = 3
Case "": .Interior.ColorIndex = 2

End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub



"Bill Pfister" wrote:

Danny, are you certain that the condition is true (can you step
through
the
code and watch it hit the ".Font.ColorIndex = 45" portion? Can you
posst the
surrounding lines of code?

Regards,
Bill



"Danny" wrote:

I have set up worksheet event code in order to format cells based
on
text
input.

All works well, but now I would also like the text to match the
cell
color
in order to hide the text. I have not had any luck getting the
font
color to
move off black. I am using the following code line: Case "fair":
.Font.ColorIndex = 45

Looking forward to the help!

Danny