Run macro automatically when value changes

[lunatu]

Hi,

Im trying to run a macro if value in column K changes into "Sent". This macro is working fine if i define only one cell (for example Set Target = Range("K15")) but not if I try to expand the range for entire column. What I am doing wrong with this macro?

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("K:K")
If Target.Value = "Sent" Then
Call CopyCell
Call RemoveDuplicates
End If
End Sub

Thanks in advance!

 

[Joe4]

Don't mess with "Target". It is a system-defined range, specifically the cell that is change that called the VBA code to run.
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
    Set rng = Intersect(Range("K:K"), Target)
    
    If rng Is Nothing Then Exit Sub
    
    For Each cell In rng
        If cell.Value = "Sent" Then
            Application.EnableEvents = False
            Call CopyCell
            Call RemoveDuplicates
            Application.EnableEvents = True
        End If
    Next cell
    
End Sub

 

[lunatu]

Works perfectly - thanks a lot for your help!

 

[Joe4]

You are welcome!