Fill Color in Functions

  • Thread starter Thread starter Thomas L
  • Start date Start date
T

Thomas L

I am using Excel 2002, and I don't know if this is possible or not, bu
I am trying to create an IF function, where the outcome of the functio
makes the fill color a certain color. I am trying to create
commission tracker for my employee's and need to be able to have th
fill color a certain color depending on the outcome.

For example, this is my function:

=IF(J5>200%,"12%",IF(J5>165%,"10%",IF(J5>135%,"8%",IF(J5>115%,"7%",IF(J5>100%,"6%",IF(J5>70%,"4%","0%"))))))

and I need the fill color to be green for the 12%, yellow for the 10%
8%, 7% and red for the 6%, 5%, and 0%. Is this possible or am
reaching beyond the limits of Excel?? If not able to do the fil
color, is there a way to do the font instead?

Any assistance provided is greatly appreciated!
 
Thomas,
You could write a function to select the color:

Private Function SelectColor(v As Double) As Integer
Const clRed = 3, clYellow = 27, clGreen = 4
Select Case v
Case Is > 2 '12%
SelectColor = clGreen
Case Is > 1.65 '10%
SelectColor = clYellow
Case Is > 1.35 '8%
SelectColor = clYellow
Case Is > 1.15 '7%
SelectColor = clYellow
Case Is > 1 '6%
SelectColor = clRed
Case Is > 0.7 '4%
SelectColor = clRed
Case Else
SelectColor = clRed
End Select
End Function

and then write an event handler to act when you updated the values:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("J5:J9")) Is Nothing Then
Target.Offset(, 1).Interior.ColorIndex =
SelectColor(Target.Value)
End If
End Sub

After validating the range should be colored, set the color as
required. The specific ranges depend on the layout of your worksheet.
This sample assumes that the formula was adjacent one column.

HTH,
rick
 
Back
Top