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
*******************

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
*******************

Thanks Norman,

Unfortunatelly it doesn't work. When reaching # .Columns(2).Value =
(Now)". # the error kicks in and the colour isn't changed.

Any other suggestions?

TIA Rob

Hi Dqsp,

The suggested code works for me.

If you wish, I can send you a copy of my test
workbook, in reesponse to an email to:

norman_jones@NOSPAMbtconnectDOTcom


(Delete "NOSPAM" and replace "DOT" with a full stop [period] )


---
Regards.
Norman


"dqsp" wrote in message
...
Thanks Norman,

Unfortunatelly it doesn't work. When reaching # .Columns(2).Value =
(Now)". # the error kicks in and the colour isn't changed.

Any other suggestions?

TIA Rob

Hi Ddsp

Perhaps, more usefully, you could send
me a copy of your problematic workbook;
remove any sensitivr data.



---
Regards.
Norman


"Norman Jones" wrote in message
...
Hi Dqsp,

The suggested code works for me.

If you wish, I can send you a copy of my test
workbook, in reesponse to an email to:

norman_jones@NOSPAMbtconnectDOTcom


(Delete "NOSPAM" and replace "DOT" with a full stop [period] )


---
Regards.
Norman

Hi Norman,

The workbook always worked fine for me as well, until recently.
It's used on a Citrix PS 4 SBC-environment with Excel 2003. Just now I
tried the same workbook at home and it doesn't work either. I'm
sending it to your e-mailadres.
Thanks for helping!

Rob

On 29 mei, 19:59, "Norman Jones"
wrote:
Hi Ddsp

Perhaps, more usefully, you could send
me a copy of your problematic workbook;
remove any sensitivr data.

---
Regards.
Norman

"Norman Jones" wrote in message

...



Hi Dqsp,

The suggested code works for me.

If you wish, I can send you a copy of my test
workbook, in reesponse to an email to:

* * * * norman_jones@NOSPAMbtconnectDOTcom

(Delete "NOSPAM" and replace "DOT" with a full stop [period] )

---
Regards.
Norman- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -