Case function substitute

M

Mariano

Does anyone have any semi-easy to follow VBA code they have written to create
a Case function?
I think I understand most of it, but seeing code I know functions properly
will probably tie up some loose ends.

thanks!
 
D

Dave Peterson

Here's a small example:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

If Target.Cells.Count > 1 Then Exit Sub

On Error GoTo ws_exit:

Set rng = Application.Intersect(Target, Me.Range("a:a"))
If rng Is Nothing Then Exit Sub

With Target
Select Case LCase(.Value)
Case Is = "monday": .Interior.ColorIndex = 3
Case Is = "tuesday": .Interior.ColorIndex = 4
Case Is = "wednesday": .Interior.ColorIndex = 5
Case Is = "thursday": .Interior.ColorIndex = 7
Case Is = "friday": .Interior.ColorIndex = 6
Case Is = "saturday": .Interior.ColorIndex = 8
Case Is = "sunday": .Interior.ColorIndex = 13
Case Else
.Interior.ColorIndex = xlNone
End Select
End With

ws_exit:

End Sub

But if you search the *excel* newsgroup in google groups for "select case",
you'll find a lot more to look at.
 
M

Mariano

Thanks Dave, thats perfect

Dave Peterson said:
Here's a small example:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

If Target.Cells.Count > 1 Then Exit Sub

On Error GoTo ws_exit:

Set rng = Application.Intersect(Target, Me.Range("a:a"))
If rng Is Nothing Then Exit Sub

With Target
Select Case LCase(.Value)
Case Is = "monday": .Interior.ColorIndex = 3
Case Is = "tuesday": .Interior.ColorIndex = 4
Case Is = "wednesday": .Interior.ColorIndex = 5
Case Is = "thursday": .Interior.ColorIndex = 7
Case Is = "friday": .Interior.ColorIndex = 6
Case Is = "saturday": .Interior.ColorIndex = 8
Case Is = "sunday": .Interior.ColorIndex = 13
Case Else
.Interior.ColorIndex = xlNone
End Select
End With

ws_exit:

End Sub

But if you search the *excel* newsgroup in google groups for "select case",
you'll find a lot more to look at.
Click to expand...
 
Top