Condition formula

[Herb]

I am trying to develop a formula that will provide me a color code based upon
the value of a given cell. For example if the value shown in cell A1 is
greater than -5% than the status is Blue. If the value in cell A1 is between
-5% and 5% than the status is Green. If the value in cell A1 is between 5%
and 10% than the status is Yellow. An finally, if the value in cell A1 is
greater than 10% than the status is red.
The status is based upon the variance of two numbers. Hope that makes sense.

 

[Mike H]

Hi,

I believe that in Excel 2997 you can do this with conditional formaating but
I don't have that so have assumed 2003. Right click your sheet tab, view code
and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Select Case Target.Value
Case Is > 0.1
icolor = 3
Case Is >= 0.05
icolor = 6
Case Is >= -0.05
icolor = 4
Case Is < -0.05
icolor = 5
Case Else
icolor = xlNone
End Select
Target.Interior.ColorIndex = icolor
End Sub

Mike

 

[Pimamedic]

what about using conditionally formatting the cells to reflect that
information Cell value is then the desired ranges