| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hi all,
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("C1:C50")) Is Nothing Then With Target Select Case UCase(.Value) Case "A": .Interior.ColorIndex = 3 Case "B": .Interior.ColorIndex = 4 Case "C": .Interior.ColorIndex = 5 Case "D": .Interior.ColorIndex = 6 Case "E": .Interior.ColorIndex = 7 Case "F": .Interior.ColorIndex = 8 Case "G": .Interior.ColorIndex = 9 Case "H": .Interior.ColorIndex = 10 Case "I": .Interior.ColorIndex = 11 Case "J": .Interior.ColorIndex = 12 Case "K": .Interior.ColorIndex = 13 ' Selection.Font.ColorIndex = 3 End Select End With End If ws_exit: Application.EnableEvents = True End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200805/1 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hi all,
Is there a question associated with this?
"Francois via OfficeKB.com" wrote: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("C1:C50")) Is Nothing Then With Target Select Case UCase(.Value) Case "A": .Interior.ColorIndex = 3 Case "B": .Interior.ColorIndex = 4 Case "C": .Interior.ColorIndex = 5 Case "D": .Interior.ColorIndex = 6 Case "E": .Interior.ColorIndex = 7 Case "F": .Interior.ColorIndex = 8 Case "G": .Interior.ColorIndex = 9 Case "H": .Interior.ColorIndex = 10 Case "I": .Interior.ColorIndex = 11 Case "J": .Interior.ColorIndex = 12 Case "K": .Interior.ColorIndex = 13 ' Selection.Font.ColorIndex = 3 End Select End With End If ws_exit: Application.EnableEvents = True End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200805/1 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hi all,
What is the question?
Stefi €˛Francois via OfficeKB.com€¯ ezt Ć*rta: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("C1:C50")) Is Nothing Then With Target Select Case UCase(.Value) Case "A": .Interior.ColorIndex = 3 Case "B": .Interior.ColorIndex = 4 Case "C": .Interior.ColorIndex = 5 Case "D": .Interior.ColorIndex = 6 Case "E": .Interior.ColorIndex = 7 Case "F": .Interior.ColorIndex = 8 Case "G": .Interior.ColorIndex = 9 Case "H": .Interior.ColorIndex = 10 Case "I": .Interior.ColorIndex = 11 Case "J": .Interior.ColorIndex = 12 Case "K": .Interior.ColorIndex = 13 ' Selection.Font.ColorIndex = 3 End Select End With End If ws_exit: Application.EnableEvents = True End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200805/1 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hi all,
Oh dear, Sorry this post was meant to be deleted......duplicated
Francois wrote: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("C1:C50")) Is Nothing Then With Target Select Case UCase(.Value) Case "A": .Interior.ColorIndex = 3 Case "B": .Interior.ColorIndex = 4 Case "C": .Interior.ColorIndex = 5 Case "D": .Interior.ColorIndex = 6 Case "E": .Interior.ColorIndex = 7 Case "F": .Interior.ColorIndex = 8 Case "G": .Interior.ColorIndex = 9 Case "H": .Interior.ColorIndex = 10 Case "I": .Interior.ColorIndex = 11 Case "J": .Interior.ColorIndex = 12 Case "K": .Interior.ColorIndex = 13 ' Selection.Font.ColorIndex = 3 End Select End With End If ws_exit: Application.EnableEvents = True End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200805/1 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hi all,
If you come back to this thread, you might find it interesting that this
part of your code... Select Case UCase(.Value) Case "A": .Interior.ColorIndex = 3 Case "B": .Interior.ColorIndex = 4 Case "C": .Interior.ColorIndex = 5 Case "D": .Interior.ColorIndex = 6 Case "E": .Interior.ColorIndex = 7 Case "F": .Interior.ColorIndex = 8 Case "G": .Interior.ColorIndex = 9 Case "H": .Interior.ColorIndex = 10 Case "I": .Interior.ColorIndex = 11 Case "J": .Interior.ColorIndex = 12 Case "K": .Interior.ColorIndex = 13 ' Selection.Font.ColorIndex = 3 End Select can be replaced by this shorter piece of code... If .Value Like "[A-Ka-k]" Then .Interior.ColorIndex = Asc(UCase(.Value)) - 62 End If I wasn't sure what the commented out statement was for; but if you meant it to go in a Case Else block, then you would duplicate that by adding an Else section to the If-Then block to handle it. Rick "Francois via OfficeKB.com" <u18959@uwe wrote in message news:849484eaf04b5@uwe... Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("C1:C50")) Is Nothing Then With Target Select Case UCase(.Value) Case "A": .Interior.ColorIndex = 3 Case "B": .Interior.ColorIndex = 4 Case "C": .Interior.ColorIndex = 5 Case "D": .Interior.ColorIndex = 6 Case "E": .Interior.ColorIndex = 7 Case "F": .Interior.ColorIndex = 8 Case "G": .Interior.ColorIndex = 9 Case "H": .Interior.ColorIndex = 10 Case "I": .Interior.ColorIndex = 11 Case "J": .Interior.ColorIndex = 12 Case "K": .Interior.ColorIndex = 13 ' Selection.Font.ColorIndex = 3 End Select End With End If ws_exit: Application.EnableEvents = True End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200805/1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
