Nested if's in vba
 

This code (when split into two seperate codes works fine):
The first automatically inserts date and time in the adjacent cells, while
the second changes cell colour depending on which (week)day it is:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("C7:c5000")) Is Nothing Then
With Target
If .Value < "" Then
.Offset(0, -1).Value = Format(Now, "hh:mm:ss")
.Offset(0, -2).Value = Format(Date, "dd/mmm")
End If
End With
End If

If Not Intersect(Target, Me.Range("A7:A5000")) Is Nothing Then
With Target
Select Case Application.Weekday(.Value, 2)
Case 1: .Interior.ColorIndex = 19
Case 2: .Interior.ColorIndex = 34
Case 3: .Interior.ColorIndex = 38
Case 4: .Interior.ColorIndex = 40
Case 5: .Interior.ColorIndex = 44
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

I have tried to add both codes but when the date is automatically inserted
by code rather than input by the user, no colour change is initiated.
Is there a way around this?
Thanks
--
tia

Jock

Nested if's in vba
 

Repeat (with modification) the coloring code in the first IF...THEN
block, or put the coloring code in a seperate Sub and have both
IF...THEN blocks call it.

Hth,
Merjet

Nested if's in vba
 

The problem is that Excel doesn't react to the automatic insertion
because of the line
Application.EnableEvents = False
Put the line
Application.EnableEvents = True
immediatly before the line
..Offset(0, -2).Value = Format(Date, "dd/mmm")
this will trigger your function again, but now with the target being
in column A.

DQ

Nested if's in vba
 

Brilliant! Thank you
--
tia

Jock


"dq" wrote:

The problem is that Excel doesn't react to the automatic insertion
because of the line
Application.EnableEvents = False
Put the line
Application.EnableEvents = True
immediatly before the line
..Offset(0, -2).Value = Format(Date, "dd/mmm")
this will trigger your function again, but now with the target being
in column A.

DQ