Use Cell Instead of Column

[adamsm]

Hi the following code calculates age as date of birth is entered in column D and puts date on column E.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 4 Then
        Target.Offset(, 1).Formula = "=IF(DATEDIF(RC[-1],NOW(),""y"")>0,DATEDIF(RC[-1],NOW(),""y"") & "" years"",IF(DATEDIF(RC[-1],NOW(),""m"")>0,DATEDIF(RC[-1],NOW(),""ym"") & "" months"",DATEDIF(RC[-1],NOW(),""y"")))"
    End If
End Sub

How could I make the code so that when the user places the date in cell M12, the age gets calculated in cell I14?

Any help would be kindly appreciated.

 

[Richard Schollar]

Will the DoB always be entered in M12? If so then:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$M$12" Then
        Range("I14").Formula = "=IF(DATEDIF(M12,NOW(),""y"")>0,DATEDIF(M12,NOW(),""y"") & "" years"",IF(M12,NOW(),""m"")>0,DATEDIF(M12,NOW(),""ym"") & "" months"",DATEDIF(M12,NOW(),""y"")))"
    End If
End Sub

 

[adamsm]

Yes date of birth will M12 but im getting the debug message highlighting the line

Range("I14").Formula = "=IF(DATEDIF(M12,NOW(),""y"")>0,DATEDIF(M12,NOW(),""y"") & "" years"",IF(M12,NOW(),""m"")>0,DATEDIF(M12,NOW(),""ym"") & "" months"",DATEDIF(M12,NOW(),""y"")))"
</pre>why is this?

 

[Norie]

Do you just want the age or do you need the formula?

 

[adamsm]

Thanks for the help.
It works fine now.

 

[Richard Schollar]

I managed to delete part of your formula - it should have been:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$M$12" Then
        Range("I14").Formula = "=IF(DATEDIF(M12,NOW(),""y"")>0,DATEDIF(M12,NOW(),""y"") & "" years"",IF(DATEDIF(M12,NOW(),""m"")>0,DATEDIF(M12,NOW(),""ym"") & "" months"",DATEDIF(M12,NOW(),""y"")))"
    End If
End Sub

 

[adamsm]

Thanks for the help. Once again.