B
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
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