select case statement

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

"jrd269" <jrd269.1q206b_1117807519.5315@excelforum-nospam.com> wrote in
message news:jrd269.1q206b_1117807519.5315@excelforum-nospam.com...
>
> 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

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

--
Don Guillett
SalesAid Software
donaldb@281.com
"jrd269" <jrd269.1q206b_1117807519.5315@excelforum-nospam.com> wrote in
message news:jrd269.1q206b_1117807519.5315@excelforum-nospam.com...
>
> 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
>
>
> --
> jrd269
> ------------------------------------------------------------------------
> jrd269's Profile:
http://www.excelforum.com/member.php...o&userid=23815
> View this thread: http://www.excelforum.com/showthread...hreadid=376273
>

Damien,
thanks man that worked beautifully.
-Joe

ROUND() chokes on text. One possible solution:

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

In article <jrd269.1q206b_1117807519.5315@excelforum-nospam.com>,
jrd269 <jrd269.1q206b_1117807519.5315@excelforum-nospam.com> wrote:

> 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)_