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

Hi
try the following:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngMyTarget As Range, rngDepends As Range

On Error Resume Next
Dim bytColorIndex As Byte
if target.column<10 the exit sub
if target.cells.count1 then exit sub
Select Case target.value
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
target.offset(, -8).Resize(, 9).Interior.ColorIndex = bytColorIndex

End Sub

--
Regards
Frank Kabel
Frankfurt, Germany


samst wrote:
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