Nested if's in vba

[Guest]

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

 

[merjet]

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

 

[dq]

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

 

[Guest]

Brilliant! Thank you