Private Sub Worksheet_Calculate()
How can I add these two lines to this code below

Range("M1") = Now
Range("O1") = Now

When I try to add them the code just hangs

Thanks



Dim i As Integer
For i = 4 To 500
Call Worksheet_Change(Cells(i, 9))
Next
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Line As Range
On Error Resume Next
With Target
If .Row = 4 And .Row <= 500 And .Column = 9 Then
Set Line = Range(Cells(.Row, 1), Cells(.Row, 13))
Select Case .Value
Case vbNullString
Line.Interior.ColorIndex = xlNone
Case "POQA"
Line.Interior.ColorIndex = 15
Case "IN PROCESS"
Line.Interior.ColorIndex = 22
Case "INSERTING"
Line.Interior.ColorIndex = 40
Case "STMTHAND"
Line.Interior.ColorIndex = 38
Case "OD-M34"
Line.Interior.ColorIndex = 50
Case Else
Line.Interior.ColorIndex = 20
End Select
End If
End With
End Sub

I presume you want to add the current date/time , if so use Now() = with
brackets .


"Little Penny" wrote in message
...
Private Sub Worksheet_Calculate()
How can I add these two lines to this code below

Range("M1") = Now
Range("O1") = Now

When I try to add them the code just hangs

Thanks



Dim i As Integer
For i = 4 To 500
Call Worksheet_Change(Cells(i, 9))
Next
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Line As Range
On Error Resume Next
With Target
If .Row = 4 And .Row <= 500 And .Column = 9 Then
Set Line = Range(Cells(.Row, 1), Cells(.Row, 13))
Select Case .Value
Case vbNullString
Line.Interior.ColorIndex = xlNone
Case "POQA"
Line.Interior.ColorIndex = 15
Case "IN PROCESS"
Line.Interior.ColorIndex = 22
Case "INSERTING"
Line.Interior.ColorIndex = 40
Case "STMTHAND"
Line.Interior.ColorIndex = 38
Case "OD-M34"
Line.Interior.ColorIndex = 50
Case Else
Line.Interior.ColorIndex = 20
End Select
End If
End With
End Sub

Your problem is you are entering an infinite loop. You make a change
to your worksheet to enter this procedure. Then, in your procedure,
you make a change to Range("M1") and Range("O1").... these in turn
call your procedure again, which results in Range("M1") and
Range("O1") being changed again, which continues ad infinim.

What you want to do is this:

Application.EnableEvents = False
Range("M1") = Now
Range("O1") = Now
Application.EnableEvents = True

This disables all your event handlers until you make these two
changes -- that way your Worksheet_Change() macro doesn't
get recursively called.

Scott

Little Penny wrote:
Private Sub Worksheet_Calculate()
How can I add these two lines to this code below

Range("M1") = Now
Range("O1") = Now

When I try to add them the code just hangs

Thanks



Dim i As Integer
For i = 4 To 500
Call Worksheet_Change(Cells(i, 9))
Next
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Line As Range
On Error Resume Next
With Target
If .Row = 4 And .Row <= 500 And .Column = 9 Then
Set Line = Range(Cells(.Row, 1), Cells(.Row, 13))
Select Case .Value
Case vbNullString
Line.Interior.ColorIndex = xlNone
Case "POQA"
Line.Interior.ColorIndex = 15
Case "IN PROCESS"
Line.Interior.ColorIndex = 22
Case "INSERTING"
Line.Interior.ColorIndex = 40
Case "STMTHAND"
Line.Interior.ColorIndex = 38
Case "OD-M34"
Line.Interior.ColorIndex = 50
Case Else
Line.Interior.ColorIndex = 20
End Select
End If
End With
End Sub

Actually, looking at your code again, you should probably put the
Application.EnableEvents = False at the beginning of your
Worksheet_Change
function, and Application.EnableEvents = True at the end, since you are
making other changes to your worksheet within the procedure.

The idea is that you want to disable events before you make any
changes,
then re-enable them afterwards. You likely won't have noticed a
problem in
your Worksheet_Change macro so far, but it also is getting called
repeatedly.
The difference is that Excel can detect the recursion to a certain
extent if
only one procedure is getting called (ie. Worksheet_Change is called
repeatedly). If you had a counter or some summation type activity in
this
function, you would get incorrect results because it would get
incremented
more than once for every change. It is good practice to disable events

inside the events if you're doing something that will itself cause one
of your
events.

Hopefully that makes sense.

Scott

Scott wrote:
Your problem is you are entering an infinite loop. You make a change
to your worksheet to enter this procedure. Then, in your procedure,
you make a change to Range("M1") and Range("O1").... these in turn
call your procedure again, which results in Range("M1") and
Range("O1") being changed again, which continues ad infinim.

What you want to do is this:

Application.EnableEvents = False
Range("M1") = Now
Range("O1") = Now
Application.EnableEvents = True

This disables all your event handlers until you make these two
changes -- that way your Worksheet_Change() macro doesn't
get recursively called.

Scott

Little Penny wrote:
Private Sub Worksheet_Calculate()
How can I add these two lines to this code below

Range("M1") = Now
Range("O1") = Now

When I try to add them the code just hangs

Thanks



Dim i As Integer
For i = 4 To 500
Call Worksheet_Change(Cells(i, 9))
Next
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Line As Range
On Error Resume Next
With Target
If .Row = 4 And .Row <= 500 And .Column = 9 Then
Set Line = Range(Cells(.Row, 1), Cells(.Row, 13))
Select Case .Value
Case vbNullString
Line.Interior.ColorIndex = xlNone
Case "POQA"
Line.Interior.ColorIndex = 15
Case "IN PROCESS"
Line.Interior.ColorIndex = 22
Case "INSERTING"
Line.Interior.ColorIndex = 40
Case "STMTHAND"
Line.Interior.ColorIndex = 38
Case "OD-M34"
Line.Interior.ColorIndex = 50
Case Else
Line.Interior.ColorIndex = 20
End Select
End If
End With
End Sub

Penny this has been answered in the thread you posted with a slightly
different title but entirely the same content!


Regards,
Simon


--
Simon Lloyd

At the time you posted this... the answer in the other thread was
inadequate.

Scott

Simon Lloyd wrote:
Penny this has been answered in the thread you posted with a slightly
different title but entirely the same content!


Regards,
Simon


--
Simon Lloyd