ExcelBanter - select case statement

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - select case statement (https://www.excelbanter.com/excel-programming/330859-select-case-statement.html)

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's Profile: http://www.excelforum.com/member.php...o&userid=23815
View this thread: http://www.excelforum.com/showthread...hreadid=376273

select case statement

"jrd269" wrote in
message ...

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

select case statement

Damien,
thanks man that worked beautifully.
-Jo

--
jrd26
-----------------------------------------------------------------------
jrd269's Profile: http://www.excelforum.com/member.php...fo&userid=2381
View this thread: http://www.excelforum.com/showthread.php?threadid=37627

select case statement

ROUND() chokes on text. One possible solution:

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

In article ,
jrd269 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)_