subtract today's date from a date in another column

[elsuji]

Dear Team,

I am having dates in Worksheet Sheet1 column D. I want to subtract Todays date from a date in column D and update on Column L.

For that i am trying the following code. But it is not working


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastRow As Long, i As Long
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
For i = 2 To LastRow
.Range("L" & i).Value = Date - Range("D" & i).Value
Next i
End With
End Sub




Can any one tell me what is the mistake on above code

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 4 Then Exit Sub

With Me.Cells(Target.Row, "L")

.Value = Date - Target.Value
.NumberFormat = "General"
End With
End Sub

[elsuji]

Dear XLD,

Thanks for your reply.

When is trying to upload my datas and using the above your code i am getting "Type Mismatch" error.

I attached my files here for your kind reference.

Kindly check and update

[SamT]

Can any one tell me what is the mistake on above code
Probably the missing Dot before Range. Try this

.Cells(i, "L") = DateDiff("d", Date, CDate(.Range("D" & i)))

[Paul_Hossler]

Little more elaborate



Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range


Application.EnableEvents = False


For Each r In Intersect(Target, Me.UsedRange).Cells
With r
If .Column <> 4 Then GoTo NextCell
If Not IsDate(.Value) Then GoTo NextCell


.Offset(0, 8).Value = Date - .Value
.Offset(0, 8).NumberFormat = Application.International(xlGeneralFormatName)
End With
NextCell:
Next


Application.EnableEvents = True
End Sub

[avichandana]

i have written

=Today() in cell A1.
anydate in cell B1
Then A1-B1 in cell C1 and changed it to datatype General