Dynamically add comment based on two cell values in a table

[mwtinec]

Hi gang,
I'm a long time excel user but novice VBA writer.

I've been working on this VBA and have cobbled together something that works when the data is in a range (for test purposes). Now I need to make this work within an Excel table for regular use. The table syntax is tripping me up. I'm hoping someone here can help.


I'm referencing two different columns in the table that are side by side. [Support Status] is the left column and [Extra Resources Needed] is the right. Anytime the worksheet changes it triggers this code. If I can get the two cases outlined below working then I can copy and modify to suit my needs for those not shown.


Here's what I have:

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim rng As Range, cell As Range


    On Error GoTo haveError


    'see if any changes are in the monitored range...
    Set rng = Application.Intersect(Target, Me.Range("e3:e14"))


    If Not rng Is Nothing Then
    'Next line prevents code updates from re-triggering this...
    '  (Not really needed if you're only adding comments)
    Application.EnableEvents = False


    For Each cell In Sheets("MyTestSheet").Range("DemandEntryTable[[#data],[Extra Resources Needed]]")


         'CASE 1
        'IF [Support Status] = "Fully Support" AND [Extra Resources Needed] = greater than zero OR text OR blank/null
        'THEN add comment to [Extra Resources Needed] stating "Either change this cell to zero OR change Support Status value"
[INDENT]If cell.Value > "0" And _[/INDENT]
[INDENT]Sheets("MyTestSheet").Range("DemandEntryTable[[#headers],[Support Status]]").Offset(cell.Row - 1).Text = "Fully Support" Then[/INDENT]
            cell.AddComment "Either change this cell to zero OR change Support Status value"
          

        
        'CASE 2 
        'IF [Support Status] = "Cannot Support" AND [Extra Resources Needed] = greater than zero
        'THEN clear comment in [Extra Resources Needed]
        

        
        End If
        
    Next
    Application.EnableEvents = True


    End If


    Exit Sub


haveError:
    MsgBox Err.Description
    Application.EnableEvents = True


End Sub

 

[igold]

Hi mwtinec,

This is a good resource for accessing different parts of tables. You should be able to get the info you need to change your code...

https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables

I hope this helps.