| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Event code won't work | Excel Discussion (Misc queries) | |||
| Workbook event code won't work on laptop | Excel Worksheet Functions | |||
| Condition Formatting in code. How? | Excel Programming | |||
| Condition Formatting in code. How? | Excel Programming | |||
| Condition Formatting in code. How? | Excel Programming | |||
Linear Mode
