Insert / Update datetime on active row when changes are made in a range

[sashapixie]

Hi

I have some VBA to insert / update a column on the active row when changes are made within a range on the active row. The vba is working when I use cell references, however when I use table named ranges it errors.

Private Sub Worksheet_Change(ByVal Target As Range)



'Update CRM date when cells change in CRM range
Dim CRM As Range, CRM_Rng As Range, Update_CRM As Range, CRM_l As Long, CRM_Cells As String


CRM_l = ActiveCell.Row
CRM_Cells = "mapping_table[CRM - Updated Date]" & CRM_l


Set CRM = Intersect(Application.ActiveSheet.Range("mapping_table[[CRM - Table Name (M)]:[CRM - Developer]]"), Target)
Set Update_CRM = Range(CRM_Cells)
If Not CRM Is Nothing Then
Application.EnableEvents = False
For Each CRM_Rng In CRM
If Not VBA.IsEmpty(CRM_Rng.Value) Then
Update_CRM.Value = Now
Update_CRM.NumberFormat = "dd-mm-yyyy, hh:mm:ss"
Else
Update_CRM.ClearContents
End If
Next
Application.EnableEvents = True
End If
End Sub

Can anyone help please

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
'Update CRM date when cells change in CRM range
Dim CRM As Range, CRM_Rng As Range, Update_CRM As Range, CRM_l As Long, CRM_Cells As String


CRM_l = Target.Row


Set CRM = Intersect(Range("mapping_table[[CRM - Table Name (M)]:[CRM - Developer]]"), Target)
Set Update_CRM = Me.ListObjects("mapping_table").ListColumns("CRM - Updated Date").DataBodyRange
If Not CRM Is Nothing Then
    Application.EnableEvents = False
    For Each CRM_Rng In CRM
        If Not IsEmpty(CRM_Rng.Value) Then
            With Intersect(Update_CRM, CRM_Rng.EntireRow)
                .Value = Now
                .NumberFormat = "dd-mm-yyyy, hh:mm:ss"
            End With
        Else
            Intersect(Update_CRM, CRM_Rng.EntireRow).ClearContents
        End If
    Next CRM_Rng
    Application.EnableEvents = True
End If
End Sub

 

[sashapixie]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
'Update CRM date when cells change in CRM range
Dim CRM As Range, CRM_Rng As Range, Update_CRM As Range, CRM_l As Long, CRM_Cells As String


CRM_l = Target.Row


Set CRM = Intersect(Range("mapping_table[[CRM - Table Name (M)]:[CRM - Developer]]"), Target)
Set Update_CRM = Me.ListObjects("mapping_table").ListColumns("CRM - Updated Date").DataBodyRange
If Not CRM Is Nothing Then
    Application.EnableEvents = False
    For Each CRM_Rng In CRM
        If Not IsEmpty(CRM_Rng.Value) Then
            With Intersect(Update_CRM, CRM_Rng.EntireRow)
                .Value = Now
                .NumberFormat = "dd-mm-yyyy, hh:mm:ss"
            End With
        Else
            Intersect(Update_CRM, CRM_Rng.EntireRow).ClearContents
        End If
    Next CRM_Rng
    Application.EnableEvents = True
End If
End Sub

Perfect!!

Thank you

 

[Fluff]

You're welcome & thanks for the feedback