Cell Change in Table

[JeannetteE]

Hi,

The following code does exactly what I need it to do when the relevant Cells change, however I need it to represent a Table. Table Name and Table Headers are commented in the Code. Any help greatly appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)


' Table Name = tblSMSubCategories
' Column G Table Header = Sub-Category
' Column I Table Header = Incident Type
' Column M Table Header = Specifics Type
' Column P Table Header = Ticket Creation Action to Run




    If Target.Address = "G10" Then
        If Range("I10").Value = "Service Request" And Range("M10").Value = "" Then
            Range("M10").Value = "Select from List"
        Else
        If Range("I10").Value = "Incident" Then
        Range("M10").Value = ""
        End If
        End If
    End If


    If Target.Address = "M10" Then
        If Range("M10").Value = "Specifics - Product Catalogue" And Range("P10").Value = "" Then
            Range("P10").Value = "Select from List"
        Else
        Range("P10").Value = ""
        End If
    End If


End Sub

 

[DanteAmor]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim sh As Worksheet, lo As ListObject, ini As Long, n As Long
    
    Set sh = ActiveSheet
    Set lo = sh.ListObjects("tblSMSubCategories")
    ini = lo.Range.Row


    ' Table Name = tblSMSubCategories
    ' Column G Table Header = Sub-Category
    ' Column I Table Header = Incident Type
    ' Column M Table Header = Specifics Type
    ' Column P Table Header = Ticket Creation Action to Run


    If Target.Count > 1 Then Exit Sub
    n = Target.Row - ini
    If Not Intersect(Target, Range("tblSMSubCategories[Sub-Category]")) Is Nothing Then
        If lo.DataBodyRange(n, lo.ListColumns("Incident Type").Index).Value = "Service Request" And _
           lo.DataBodyRange(n, lo.ListColumns("Specifics Type").Index).Value = "" Then
            lo.DataBodyRange(n, lo.ListColumns("Specifics Type").Index).Value = "Select from List"
        Else
            lo.DataBodyRange(n, lo.ListColumns("Specifics Type").Index).Value = ""
        End If
    End If


    If Not Intersect(Target, Range("tblSMSubCategories[Specifics Type]")) Is Nothing Then
        If lo.DataBodyRange(n, lo.ListColumns("Specifics Type").Index).Value = "Specifics - Product Catalogue" And _
           lo.DataBodyRange(n, lo.ListColumns("Ticket Creation Action to Run").Index).Value = "" Then
            lo.DataBodyRange(n, lo.ListColumns("Ticket Creation Action to Run").Index).Value = "Select from List"
        Else
            lo.DataBodyRange(n, lo.ListColumns("Ticket Creation Action to Run").Index).Value = ""
        End If
    End If
End Sub

 

[JeannetteE]

Thank you so much, this works brilliantly

 

[gravanoc]

This is off-topic, but did you notice your columns spell out GIMP? Made me think of my photo editing app.

 

[DanteAmor]

Thank you so much, this works brilliantly

I'm glad to help you. Thanks for the feedback.

 

[DanteAmor]

This is off-topic, but did you notice your columns spell out GIMP? Made me think of my photo editing app.

I had not noticed