Worksheet_Change
 

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

Worksheet_Change
 

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

Worksheet_Change
 

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

Worksheet_Change
 

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

Worksheet_Change
 

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


Regards,
Simon


--
Simon Lloyd

Worksheet_Change
 

Good catch Martin... I didn't notice that bit. Martin has a good point
about the way you're using Worksheet_Change.

If you want to call the code in Worksheet_Change, you might be better
to create
another subroutine that isn't one of the Event routines, something
like:

Sub Colouring (ByVal Target as Range)
... <-- All your coloring code.
End Sub

Sub Worksheet_Change(ByVal Target as Range)
Application.EnableEvents = False
Call Colouring(Target)
Application.EnableEvents = True
End Sub

This is more of an esthetic thing than it is a requirement. It's
easier to keep your Event macros (Worksheet_Change,
Worksheet_Calculate) in order so they don't have problems.

Sub Worksheet_Calculate ()
Dim i as Long

Application.EnableEvents = False
range("M1") = Now
range("O1") = Now

For i = 4 to 500
Call Colouring(Cells(i,9))
Next i
Application.EnableEvents = True
End Sub

The one question I'd have about this would be the For loop that you
have. It will run *every* time your worksheet calculates. You might
be better to split this off into a separate macro, so instead of what
you have for Worksheet_Calculate above, make it like this:

Sub Worksheet_Calculate ()
Application.EnableEvents = False
range("M1") = Now
range("O1") = Now
Application.EnableEvents = True
End Sub

Sub Initialize ()
Dim i as Long

For i = 4 to 500
Call Colouring(Cells(i,9))
Next i
End Sub

Then you could just run initialize the first time so that column will
have the appropriate colouring, and afterwards it will be looked after
by your Worksheet_Change macro.

Scott

Martin Fishlock wrote:
Little Penny

It is not recommended to call a charge event subrountine as the rountine is
called whenever a chage occurs on the sheet.

Just put the following in the sub routine, I called it ss111

Sub ss111()

' Dim i As Integer
' For i = 4 To 500
range("M1") = Now
range("O1") = Now
' Call Worksheet_Change(Cells(i, 9))
' Next
End Sub

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"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

Worksheet_Change
 

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