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