Hi There,

Here's my what I'm trying to accomplish but can't seem to get to work.

I have a user form that is used to enter data on to sheet 2. Sheet 1
takes that data and applies it to formulas in columns B through I.
Colum J then averages the results of columns B through I.
I'm trying to get the following bit of code to change the color of B
through J when ever the values change (based on the values of colum
J). But for some reason it won't work.

ANY help would be greatly appreciated and thank you in advance!!!!
sincerely,
S.
Here's the code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngMyTarget As Range, rngDepends As Range
Set rngMyTarget = [J4:J500]

On Error Resume Next
Set rngDepends = Target.Dependents
Set rngMyTarget = Intersect(rngMyTarget, rngDepends)
If rngMyTarget Is Nothing Then Exit Sub

Dim bytColorIndex As Byte

Select Case Val(rngMyTarget)
Case Is = 4.5
bytColorIndex = 4 ' Green
Case Is = 3.5
bytColorIndex = 6 ' Yellow
Case Is = 2.6
bytColorIndex = 19 ' Pale Yellow
Case Is = 0.1
bytColorIndex = 3 ' Red
Case Else
bytColorIndex = 0
End Select
rngMyTarget.Offset(, -8).Resize(, 9).Interior.ColorIndex =
bytColorIndex

End Sub