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.

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

"Herb" wrote:

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.

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

"Herb" wrote:

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.