Have a play with this. I don't know if I got the ranges quite right.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is 0.2: Num = 10 'green
Case 0.02 To 0.2: Num = 1 'black
Case -0.0199 To 0.0199: Num = 5 'blue
Case -0.0499 To -0.0199: Num = 7 'magenta
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
End Sub


Gord Dibben MS Excel MVP

On Thu, 22 Oct 2009 15:16:01 -0700, ker_01
wrote:


I have a series of values (percentages) to process through a select case
statement. Cases include:
.2 ' more than 20%
.02 AND <=.2 ' between 2% and 20%
-.0199 AND <.0199 ' between +/-2%
-.0499 AND <-.0199 ' between -5% and -2%
etc.

I'm having trouble figuring out the syntax for a case range statement. I
thought I had it with
Select Case MyVar
Case (MyVar .2)
'do stuff
Case (MyVar .02) AND (MyVar <=.2)
'do stuff
Case (MyVar -.02) AND (MyVar<=.02)

but that isn't working.

What is the proper way to use a range of values that includes [greater than]
and [less than or equal to] as an AND criteria?

Thank you!
Keith