Worksheet_Change

[Little Penny]

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

 

[MarMo]

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

 

[Scott]

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

 

[Scott]

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]

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