On cell change do code

[dado6612]

Hi all, Have a simple Table1 where in column C are Dates, in column D Status, and Column E Update.
What I wish for is that when a cell in Column E changes its value (it's data validation list) to do this:
Add value of cell to cell in column C (simple C.value=C.value+E.value*7)
Check if E.value was positive, if so
Find first "Priority" in D Status column, assign previous date value of changed cell
and at the end for any case
Set value of cell in E as blank

ex.
Initial
....A........B.......C.........D......E
[TABLE="class: grid, width: 50, align: left"]
<tbody>[TR]
[TD]Test1[/TD]
[TD]Test1[/TD]
[TD]28.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test2[/TD]
[TD]Test2[/TD]
[TD]28.2[/TD]
[TD][/TD]
[TD]E2[/TD]
[/TR]
[TR]
[TD]Test3[/TD]
[TD]Test3[/TD]
[TD]28.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test4[/TD]
[TD]Test4[/TD]
[TD]28.4[/TD]
[TD]Priority[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test5[/TD]
[TD]Test5[/TD]
[TD]28.5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Let say we change E2 to -2

....A........B.......C.........D......E
[TABLE="class: grid, width: 50, align: left"]
<tbody>[TR]
[TD]Test1[/TD]
[TD]Test1[/TD]
[TD]28.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test2[/TD]
[TD]Test2[/TD]
[TD]14.2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test3[/TD]
[TD]Test3[/TD]
[TD]28.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test4[/TD]
[TD]Test4[/TD]
[TD]28.4[/TD]
[TD]Priority[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test5[/TD]
[TD]Test5[/TD]
[TD]28.5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Date has updated by -2*7 days, now what if it was initially 2 in E2

....A........B.......C.........D....E
[TABLE="class: grid, width: 50, align: left"]
<tbody>[TR]
[TD]Test1[/TD]
[TD]Test1[/TD]
[TD]28.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test4[/TD]
[TD]Test4[/TD]
[TD]28.2[/TD]
[TD]Priority[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test2[/TD]
[TD]Test2[/TD]
[TD]14.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test3[/TD]
[TD]Test3[/TD]
[TD]28.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test5[/TD]
[TD]Test5[/TD]
[TD]28.5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Test4 took a place of Test2
The rest got auto sorted by date

I thought it's gonna be easy, started to make a code, but just keep on getting error after error and crashes.
Any help?
Thanks

 

[JLGWhiz]

I am not sure this is going to work with the date format you show in your example. But it works with the regular date format. The code goes in your sheet code module and it will run when changes are made to the worksheet, but only executes on changes made in column E.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Dim fn As Range, od As Date
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
        od = Target.Offset(, -2).Value
        Target.Offset(, -2) = Target.Offset(, -2).Value + (Target.Value * 7)
        If Target.Value > 0 Then
            Set fn = Range("D:D").Find("Priority", , xlValues)
                If Not fn Is Nothing Then
                    fn.Offset(, -1) = od
                    fn.Offset(, 1) = ""
                End If
        End If
    End If
Application.EnableEvents = True
End Sub

 

[dado6612]

I am not sure this is going to work with the date format you show in your example. But it works with the regular date format. The code goes in your sheet code module and it will run when changes are made to the worksheet, but only executes on changes made in column E.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Dim fn As Range, od As Date
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
        od = Target.Offset(, -2).Value
        Target.Offset(, -2) = Target.Offset(, -2).Value + (Target.Value * 7)
        If Target.Value > 0 Then
            Set fn = Range("D:D").Find("Priority", , xlValues)
                If Not fn Is Nothing Then
                    fn.Offset(, -1) = od
                    fn.Offset(, 1) = ""
                End If
        End If
    End If
Application.EnableEvents = True
End Sub

It works brilliantly (I've put in Target.Value = "" to clear cell)
Thanks for your work and help, just a question if you could explain what was wrong with my try

Private Sub Worksheet_Change(ByVal Target As Range)Dim k As Long, status As Range, lr As Long
Dim cell_to_test As Range, cells_changed As Range, acell As Variant
Dim acell As String
Application.ScreenUpdating = False
acell = ActiveCell.Address
acellv = acell.Value
lr = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    Set status = Range("D2", "D" & lr)
k = Application.Match("Priority", status, 0) + 1
    Set cells_changed = Target(1, 1)
    Set cell_to_test = Range("e2", "e" & lr)
    If Not Intersect(cells_changed, cell_to_test) Is Nothing Then
ActiveCell.Offset(0, -2).Value = ActiveCell.Offset(0, -2).Value + Range(acell).Value * 7
If Range(acell).Value > 0 Then
Range("c" & k).Value = acellv
End If
End If
Range(acell).ClearContents
Application.ScreenUpdating = True
End Sub

 

[dado6612]

Actually an update, the code runs on any cell change on whole sheet.

Edit: Haha forget about it, my mistake, wrong placement of target clear, all works perfectly
Thanks again

 

[JLGWhiz]

You're welcome,
Regards, JLG