VBA Case Is Values

[Rakesh Rampiar]

How do I represent the following in an event calculate type macro?

If Case Is equalt to and between 5 and -5 change colour to blue
If Case Is equal to and between 10 and -10 change colour to Green
If Case Is equal to and between 20 and -20 change colour to Yellow
etc, etc, etc...

or

If Case is equal to and between 0 and 5 change colour to blue
If Case is equal to and between -5 and 0 change colour to blue
If Case equal to and between 6 and 10 change colour to Green
If Case equal to and between -10 and -6 change colour to Green
If Case equal to and between 11 and 20 change colour to Yellow
If Case equal to and between -20and -11 change colour to Yellow
etc, etc, etc...

Below is the event macro that I wanted to update with the above mentioned
paramaters:

Private Sub Worksheet_Calculate()
Dim myCell As Range
Dim myRng As Range
Dim myColorIndex As Long
Set myRng = Me.Range("d2:h24")

For Each myCell In myRng.Cells
If IsNumeric(myCell.Value) Then
Select Case myCell.Value
Case Is < 1: myColorIndex = 0 'White
Case Is = 1: myColorIndex = 5 'Blue
Case Is <= 2: myColorIndex = 4 'Green
Case Is <= 3: myColorIndex = 6 'Yellow
Case Is <= 4: myColorIndex = 46 'Orange
Case Is <= 5: myColorIndex = 3 'Red
Case Else
myColorIndex = xlNone
End Select
Else
myColorIndex = xlNone
End If
myCell.Interior.ColorIndex = myColorIndex
Next myCell
End Sub

Thank you

Rakesh Rampiar

 

[JE McGimpsey]

One way:

Select Case Abs(myCell.Value)
Case <=5: myColorIndex = 5 ' Blue
Case <=10: myColorIndex = 4 ' Green
'etc.
End Select

 

[Don Guillett]

try this idea using abs

Sub docase()
For Each c In Range("d2:h25")
If Len(c) > 0 Then
Select Case Abs(c)
Case 0 To 5: x = 3
Case 6 To 10: x = 4
Case 11 To 15: x = 6
Case Else: x = 0
End Select
Else: x = 0
End If
c.Interior.ColorIndex = x
Next c
End Sub

 

[Rakesh Rampiar]

Thanks that worked!

Another scenario for the same macro:

What is I want to make the numbers between -5 and 0 as blue (and eqaul to)
Numbers between 1 and 5 as green (and equal to)
Numbers between 6 and 10 as green(and equal to)
Numbers between -10 and -6 as green(and equal to)
Numbers between 11 and 20 as yellow (and equal to)
Numbers between -20 and -11 as yellow (and equal to)
'etc, etc, ect...

Thank you

 

[Don Guillett]

From the answers given you should be able to figure this out.