Conditional Formatting for %

  • Thread starter Thread starter b1llt
  • Start date Start date
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
 
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
 
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 -
Click to expand...

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.
 
Back
Top