Need cell address of changed cell in filtered data

Question

I've been racking my brain on this, Googling and searching to no avail so hopefully someone can help.

I have a project where a users opens a spreadsheet which pulls data from an SQL server. If the user changes data in one of three columns that data is updated back to the SQL server.

So far I have this part working using the "Worksheet_Change" event using the following simple test code, it returns the address of the changed cell in column "A" which eventually that changed cell address will be passed to a routine to update the SQL server with the changed data.

My test code so far is...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Changed_Cell As Range
If Target.Column = 1 Then
    For Each Changed_Cell In Target
        MsgBox ("Changed_Cell : " & CStr(Changed_Cell.Address))
    Next Changed_Cell
End If
End Sub

While this works, my issue is that I need this to work with auto-filtered data as the users are fond of using auto-filters and have been told "no auto-filters are not an option".

The above code works with auto-filters if I only change one cell in the filtered range at a time. If I try to change multiple cells in the filtered range it breaks.

So what I need is something that for each changed cell in an auto-filtered column gives me the individual cell addresses of each changed cell and not a range. (hopes that makes sense)

Use For Each C in Target (C defined as Range) , so you loop through multiple cells in your Target — Shai Rado
Thanks, but if you look at my code that's what I've done. "Target" is a range and "Changed_Cell" is a range so my code line "For Each Changed_Cell In Target" is the same as what you are suggesting. — ByerRA

Unknown Unknown · Accepted Answer · 2018-04-24T22:26:45

Isolate column A from potential cells in Target.

Private Sub Worksheet_Change(ByVal Target As Range)

    If not intersect(Target, Columns(1)) is nothing Then
        Dim Changed_Cell As Range
        For Each Changed_Cell In intersect(Target, Columns(1))
            MsgBox ("Changed_Cell : " & CStr(Changed_Cell.Address))
        Next Changed_Cell
    End If

End Sub

Need cell address of changed cell in filtered data

1 Answers