Auto run macro after entering/changing the date

Hi friends,
My data is in range 'b8:ae3000'
Column 't' birthdate, column 'ae' today's age
I want to run the macro 'AgeValue' after entering/changing the date in column 't'. At present I have inserted the call event in this workbook. As a result it runs each time I open the workbook. The birthdates in column 't' are not changing daily. It changes whenever I enter a new entry of contact but it is not daily. The AgeValue macro is running each time which I don't want. I want to save the time.

So I want to run the 'AgeValue' macro after entering/changing the date in column 't' only and not each time. Is it possible? If so please suggest me a code.

Thanking you in anticipation.

Regards,

Mukesh Baviskar

Thank you. I inserted the code int the sheet modul but I don't know how to link up it with the 'AgeValue' macro. The code is:

Sub AgeValue()
Dim a
Dim I As Variant
On Error Resume Next

    With Range("T8", Range("T" & Rows.Count).End(xlUp)).Resize(, 12)
    
                a = .Value
                For I = 1 To UBound(a, 1)
                        If a(I, 1) = "" Or a(I, 1) = "-" Then
                            a(I, 12) = ""
                        Else
                            a(I, 12) = _
                            "=""Age is ""&DATEDIF(RC[-11],TODAY(),""Y"")&"" Years, ""&DATEDIF(RC[-11],TODAY(),""YM"")&"" Months and ""&DATEDIF(RC[-11],TODAY(),""MD"")&"" Days"""
                        End If
                Next
                        .Value = a
                        .Value = .Value    ' optional: convert formula to value
                                                                                            .Columns.AutoFit
                End With
Range("Aa:Ad").EntireColumn.Hidden = True
End Sub

Please guide me.

Thank you.

Regards,

Mukesh

Thank you. I'm getting error 438 Object doesn't support this type of method or property' on the line 'Cells(cell.Row, "AE").FormulaR1C1 _'.

Thank you.

Mukesh

Hi JBeaucaire,
Excellent! Now it's working fine with correct output.

Thank you for solving the problem.

Sincerely,

Mukesh

Hi JBeaucaire,
Sorry for posting here on a solved thread. But my revision is related to this thread and you created this macro so I post my revision request here. If it's breaking the forum rule please ignore it positively. I respect you and the forum rule.

I want to reduce the size of file so I want to convert the formula range 'ae8:ae3000' to value. Is it possible? I tried 'value = value' but not success.

Again sorry and request you to ignore if it didn't comply the forum rule.

Thanking you.

Sincerely,

Mukesh

Here is the code which is not converting formula into value.

            Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim formula As Variant
Dim value As Variant

Application.EnableEvents = False
If Not Intersect(Target, Range("T:T")) Is Nothing Then
    For Each cell In Intersect(Target, Range("T:T"))
        If cell.value = "" Or cell.value = "-" Then
        If cell.formula = formula Then
            Cells(cell.Row, "AE").value = ""
        Else
            Cells(cell.Row, "AE").FormulaR1C1 = _
                "=""Age is ""&DATEDIF(RC[-11],TODAY(),""Y"")&"" Years, ""&DATEDIF(RC[-11],TODAY(),""YM"")&"" Months and ""&DATEDIF(RC[-11],TODAY(),""MD"")&"" Days"""
                'Else
                Cells(cell.Row, "AE").formula = value
                                        End If
        Columns.AutoFit
    'Next cell
    End If
Next
End If
Application.EnableEvents = True
End Sub

Thank you.

Sincerely,

Mukesh

Hello JBeaucaire,
Excellent! Now it's working fine.

Thank you very much.

Have a nice day!

Sincerely,

Mukesh