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

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

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

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

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