Autorun macro when i change value in column 8 in a table

[Golaidron]

How can i get this macro to run everytime i change value in column 8 in a table.

Dim ws As Worksheet
Dim table As ListObject
Dim tblRows As ListRows
Dim tblRow As ListRow


Set ws = Sheet1
Set table = ws.ListObjects("Table1")

Set tblRows = table.ListRows

For Each tblRow In tblRows
If table.DataBodyRange(tblRow.Index, 8) = "Defect" Then
table.DataBodyRange(tblRow.Index, 10) = "New test"
ElseIf table.DataBodyRange(tblRow.Index, 8) = "Ok" Then
table.DataBodyRange(tblRow.Index, 10) = table.DataBodyRange(tblRow.Index, 6) + 366
ElseIf table.DataBodyRange(tblRow.Index, 8) = "" Then
table.DataBodyRange(tblRow.Index, 10) = ""
End If

Next tblRow

 

[lrobbo314]

Put your code in the sheet's 'Worksheet_Change' event.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H:H")) Is Nothing Then
    'Code to be executed
End If
End Sub

 

[Norie]

Try this code, which goes in the module of the worksheet which has the table on it.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim table As ListObject
Dim tblRows As ListRows
Dim tblRow As ListRow

    Set table = Me.ListObjects("Table1")

    If Intersect(Target, table.ListColumns(8).DataBodyRange) Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    
    Set tblRows = table.ListRows

    For Each tblRow In tblRows
        If table.DataBodyRange(tblRow.Index, 8) = "Defect" Then
            table.DataBodyRange(tblRow.Index, 10) = "New test"
        ElseIf table.DataBodyRange(tblRow.Index, 8) = "Ok" Then
            table.DataBodyRange(tblRow.Index, 10) = table.DataBodyRange(tblRow.Index, 6) + 366
        ElseIf table.DataBodyRange(tblRow.Index, 8) = "" Then
            table.DataBodyRange(tblRow.Index, 10) = ""
        End If

    Next tblRow
    
    Application.EnableEvents = True
    
End Sub

 

[Golaidron]

Thanks, but i get an issue with the tblRow.Index. Can this row.index be reset after en update?

 

[Norie]

What issue do you get?

Do you actually need tblRow.Index and the loop?

 

[Golaidron]

Hi Norie, I don't need tblrow.index, most of this formula is copyed from others. If you have a better idea, please share.

 

[Norie]

Do you want to make changes to other columns in the table based on what value is entered in column 8?

 

[Golaidron]

Hi Norie

This is what i want to happen.

When im in row x (in table) and make a change to column 8 (Status), then row x and column 10 will either show "ASAP" or Date in column 6 +366 days
The macro doesn't have to go through every row i table every time I change a value i column 8. Just the row i am changing.

Here is my workbook.
Autorun marco

Please help and make me look good in front of my boss.