This will work but ONLY if a change is made to the active sheet. Is that
what you want??

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'If Target.Address < "$E$2" Then Exit Sub
If Sh.Range("e2") = 120 Then
'If Target = 120 Then
Sh.Tab.ColorIndex = 46
Else
Sh.Tab.ColorIndex = xlNone
End If
End Sub


--
Don Guillett
SalesAid Software

"rob" wrote in message
...
Am I missing something? I copied what you sent and I get the same result
as
before. The only way it works is if I clear the formula and put in the
result. I even tested it in a new, blank workbook and got the same
result.
I have even inserted this statement, Sh.Range("U246").Formula =
"=(R246/T246)/24", before this statement, If Not
Application.Intersect(Target, Range("U246")) Is Nothing Then. It works
just
like I want it to, but whenever data is entered I get a run time 13
error -
type mismatch. Any suggestions on the run time error or eliminating the
formula portion?
Thanks for all your help,
rob

"Bob Phillips" wrote:

It works for me with that formula in the cell. It doesn't matter as it
checks the cell value.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"rob" wrote in message
...
Bob,
Thanks for the help. It works great as long as there is no formula in
cell
U246. I have this formula in the cell: (R246/T246)/24. If the result
is
<120, the tab should turn red. Is there a way for xl to look only at
the
result in the cell and not the formula?

Thanks again,
rob

"Bob Phillips" wrote:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Not Application.Intersect(Target, Range("U246")) Is Nothing
Then
If Target.Value = IsError(xlErrDiv0) Then
Sh.Tab.ColorIndex = xlColorIndexNone
ElseIf Target.Value < 120 Then
Sh.Tab.Color = RGB(255, 0, 0)
ElseIf Target.Value = 120 Then
Sh.Tab.ColorIndex = xlColorIndexNone
End If
End If
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"rob" wrote in message
...
I have the following macro:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target
As
Range)
If Application.Intersect(Target, Range("U246")) Is Nothing Then
If Target.Value = IsError(xlErrDiv0) Then
Sh.Tab.ColorIndex = xlColorIndexNone
Else
If Target.Value < 120 Then
Sh.Tab.Color = RGB(255, 0, 0)
Else
If Target.Value = 120 Then
Sh.Tab.ColorIndex = xlColorIndexNone
End If
End If
End If
End If
End Sub

It is suppose to change the tab color to red when cell U246 is
below
120.
Instead it changes color when a value below 120 is entered into any
cell.
I
have this code in a workbook module, so it works on all worksheets
in
the
workbook. There is a formula in cell U246. When the value of this
formula
is <120, the tab should turn red.
How do I get it to look at only cell U246 and not all cells to
decide
when
to change color?
Thanks in advance for any help,
rob