Conditional Formatting for %

[b1llt]

Wanting to make a cell change color if it hits one of these criteria:
between 1% and -1% then green
between 1% and 5% then orange
greater than 5% then purple
between -1% and -5% then yellow
less than -5% then red

What can I do to the following script to make it work?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cel As Range
Dim FormatRange As Range

Set FormatRange = Range("A1:H875")

If Not Application.Intersect(Target, FormatRange) Is Nothing Then

For Each cel In FormatRange

Select Case cel.Value
Case Is <= 0.01, Is >= -0.01
cel.Interior.ColorIndex = 4
Case Is <= 0.04999999999, Is >= 0.01000000001
cel.Interior.ColorIndex = 27
Case Is >= 0.05
cel.Interior.ColorIndex = 7
Case Is <= -0.01000000001, Is >= -0.0499999999999
cel.Interior.ColorIndex = 44
Case Is <= -0.05
cel.Interior.ColorIndex = 3
Case Else
cel.Interior.ColorIndex = xlColorIndexNone
End Select

Next cel

End If

End Sub


Much appreciated,
Bill

 

[Barb Reinhardt]

In order for this to work, you have to change a field that's in the range you
call FormatRange. If the cells in FormatRange have calculations in them,
you'll need to look at FormatRange.Precedents

HTH,
Barb Reinhardt

 

[Sri]

In order for this to work, you have to change a field that's in the rangeyou
call FormatRange.   If the cells in FormatRange have calculations in them,
you'll need to look at FormatRange.Precedents

HTH,
Barb Reinhardt
















- Show quoted text -

check the article below, i have discussed your case there.
http://funwithexcel.blogspot.com/2009/04/macro-for-conditional-formatting-in.html
let me know if this helps. Your select case conditions are the ones
that are giving you the problem.

 

[b1llt]

This worked great. I appreciate your help very much.
Thanks,
Bill