Condition formula

H

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.
 
M

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
 
P

Pimamedic

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top