Conditional Formatting for %
 

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

Conditional Formatting for %
 

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

"b1llt" wrote:

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