Hi Dqsp,

Near the head of your procedure, you
should change:

Application.EnableEvents = True

to

On Error GoTo XIT
Application.EnableEvents = False

Then replace:

End Sub


with

XIT:
Application.EnableEvents = True

End Sub

Otherwise, every cell value change effected
by the procedure will be considered as a
new change event and the procedure will r
ecursively call itself.



---
Regards.
Norman


"dqsp" wrote in message
...
Hi everyone,

The following code is used to change the colour in a cell after a
given (validated) entry. In the next cell the date of the change is
written and another cell is used for a dash or the word Yes when a
task is completed (5 purple).

This code works fine in several sheets and has been working until
recently in a sheet where I keep track of completed tasks.
What happens when I walk through the code step by step, is that the
code suddenly stops after ".Columns(2).Value = (Now)". I don't
understand what happens here. Any suggestions?

TIA, Rob
*******************
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim myRng As Range, Number As Integer
Application.EnableEvents = True
Number = Sh.Index
Select Case Number
Case 1, 2
With Target
If .Cells.Count 1 Then Exit Sub
'If (.Row = 4 And .Row <= 100 And .Column = 8 And _
.Column Mod 2 = 0 And .Column <= 15) Then
If (.Row = 4 And .Row <= 250 And .Column = 7) Then
Set myRng = Target.Offset(0, 0).Resize(1, 1)
Select Case LCase(Target.Value)
Case Is = "0 blue"
myRng.Interior.ColorIndex = 5
myRng.Font.ColorIndex = 2
.Columns(2).Value = (Now)
.Columns(3).Value = "-"
Case Is = "1 orange"
myRng.Interior.ColorIndex = 46
myRng.Font.ColorIndex = 2
.Columns(2).Value = (Now)
.Columns(3).Value = "-"
Case Is = "2 green"
myRng.Interior.ColorIndex = 4
myRng.Font.ColorIndex = 0
.Columns(2).Value = (Now)
.Columns(3).Value = "-"
Case Is = "3 yellow"
myRng.Interior.ColorIndex = 6
myRng.Font.ColorIndex = 0
.Columns(2).Value = (Now)
.Columns(3).Value = "-"
Case Is = "4 red"
myRng.Interior.ColorIndex = 3
myRng.Font.ColorIndex = 2
.Columns(2).Value = (Now)
.Columns(3).Value = "-"
Case Is = "5 purple"
myRng.Interior.ColorIndex = 39
myRng.Font.ColorIndex = 2
.Columns(2).Value = (Now)
.Columns(3).Value = "Yes"
Case Else
'Set myRng = Target.Offset(0, -1).Resize(1, 1)
'myRng.Interior.ColorIndex = xlNone
Set myRng = Target.Offset(0, 0).Resize(1, 1)
myRng.Interior.ColorIndex = xlNone
.Columns(2).Value = ""
.Columns(3).Value = "-"
End Select
End If
End With
Case Else
End Select
End Sub
*******************