select case statement

[jrd269]

I have a select case statement that recognizes numbers and can tell me
when they are out of range, but when text or anything other than
numbers are entered, the macro has a problem. It says type mismatch and
highlights the below underlined statement. My question is how can I
have it accept text, to allow a error message to pop up.

Sub color()
Dim c As Range
Dim msg, error As String
msg = "Please enter a value between 1 and 5."
error = "ERROR!"

For Each c In Selection
c.NumberFormat = "0"

_Select_Case_Application.WorksheetFunction.Round(c.Value,_3)_

Case Is = 1
c.Interior.color = vbGreen
c.Font.color = vbWhite
Case Is = 2
c.Interior.color = vbBlue
c.Font.color = vbWhite
Case Is = 3
c.Interior.color = vbYellow
c.Font.color = vbBlack
Case Is = 4
c.Interior.color = RGB(255, 153, 0)
c.Font.color = vbBlack
Case Is = 5
c.Interior.color = vbRed
c.Font.color = vbWhite
Case Else
c.Interior.color = vbBlack
c.Font.color = vbWhite
Response = MsgBox(msg, vbOKOnly, error)

End Select
Next c
End Sub

-joe

 

[Damien McBain]

I have a select case statement that recognizes numbers and can tell me
when they are out of range, but when text or anything other than
numbers are entered, the macro has a problem. It says type mismatch and
highlights the below underlined statement. My question is how can I
have it accept text, to allow a error message to pop up.

Try this:

Sub colour()

For Each c In Selection

Select Case c
Case 1
c.Interior.Color = vbGreen
c.Font.Color = vbWhite
Case 2
c.Interior.Color = vbBlue
c.Font.Color = vbWhite
Case 3
c.Interior.Color = vbYellow
c.Font.Color = vbBlack
Case 4
c.Interior.Color = RGB(255, 153, 0)
c.Font.Color = vbBlack
Case "D"
c.Interior.Color = vbRed
c.Font.Color = vbWhite
Case Else
c.Interior.Color = vbBlack
c.Font.Color = vbWhite
MsgBox "You have a selected cell with an invalid value", , "Wake up dude"

End Select
Next c
End Sub

 

[Don Guillett]

look at the help in vba and you will find
case else

 

[jrd269]

Damien,
thanks man that worked beautifully.
-Jo

 

[JE McGimpsey]

ROUND() chokes on text. One possible solution:

Select Case Application.WorksheetFunction.Round(Val(c.Value), 3)