Macro run when cell in column changes to "done"

[B-Man]

I want to create a macro so whenever a cell in column E changes to "Done" it runs the macro.

currently I have this so whenever a cell changes in column E it runs so that caused a never ending loop then crashes...

am I able to set this up to only run when it changes to "done"
the problem I see is it filters the data so the cells change again.... causing it to run again...

Any ideas?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("E:E")) Is Nothing Then



     ActiveWorkbook.Worksheets("Notes").ListObjects("Table2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Notes").ListObjects("Table2").Sort.SortFields.Add( _
        Range("Table2[Status]"), xlSortOnFontColor, xlDescending, , xlSortNormal). _
        SortOnValue.Color = RGB(208, 206, 206)
    With ActiveWorkbook.Worksheets("Notes").ListObjects("Table2").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With



End If

End Sub

 

[Eric W]

You can check for "Done" as follows:

If Target.Value = "Done" Then

I'd also suggest you use

Application.EnableEvents = False
.
.
.
Application.EnableEvents = True

at the start and end of your macro. That will prevent it from calling the event routine when the trigger happens from within the event routine.

 

[B-Man]

Cheers worked a treat.
I did have an issue

With this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

It should have been

Private Sub Worksheet_Change(ByVal Target As Range)

As it was only working on selection of cell not change explains why it was only working a small amount
Also added OR "done"

If Target.Value = "Done" or "done" Then

 

[Eric W]

Good catch on changing it to Worksheet_Change. But I think you need to change the If to

If Target.Value = "Done" or Target.Value = "done" Then

or

If LCase(Target.Value) = "done" Then

The "or" in a VBA comparison requires a full condition.