I am currently trying to devlop a new excel sheet using macros to automatically include correct dates when a specifc cell has a value.
i have used a code column "M" which works correctly but my column "O" needs changing. I only want the date to populate in column "O" when the drop down list in column "N" shows completed.
How can i change the current code to do this?
see below for current code I am using
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Updated by Extendoffice 2017/10/12
Dim xRg As Range, xCell As Range
On Error Resume Next
If (Target.Count = 1) Then
If (Not Application.Intersect(Target, Me.Range("L4:L1048576")) Is Nothing) Then _
Target.Offset(0, 1) = Date
Application.EnableEvents = False
Set xRg = Application.Intersect(Target.Dependents, Me.Range("L4:L1048576"))
If (Not xRg Is Nothing) Then
For Each xCell In xRg
xCell.Offset(0, 1) = Date
Next
End If
Application.EnableEvents = True
End If
On Error Resume Next
If (Target.Count = 1) Then
If (Not Application.Intersect(Target, Me.Range("N4:N1048576")) Is Nothing) Then _
Target.Offset(0, 1) = Date
Application.EnableEvents = False
Set xRg = Application.Intersect(Target.Dependents, Me.Range("N4:N1048576"))
If (Not xRg Is Nothing) Then
For Each xCell In xRg
xCell.Offset(0, 1) = Date
Next
End If
Application.EnableEvents = True
End If
End Sub