Conditional Macros

Hello everyone!

I have a seemingly very challenging problem that I can't seem to find the answer to.

I have two Tables that need to be worked with.

The first table (Table 1) will have the following headers: "Member", "Rank", and currently "Comment" (though I want to get rid of this).
The second table (Table 2) will have the following headers: "Rank" and "Flags" (among other things)

The way I want to enter my data is: Type the Member name, Rank, and Insert a Comment to the cell of the Member name.
I then want the cells in the "Flags" column of Table 2 checking two criteria per each row in Table 1...
1) Is there a comment in the Member's Cell?
2) Does the Rank of that Member match the Rank for this row?
...and to then to do +1 in "Flags" if both criteria are met.

Currently my "Comment" header in Table 1 is being used as a work around method that still doesn't fully work the way I want it to.

It's purpose was to utilize the following code I found online...

Function Has_Comment(mycell As Object) As Boolean

 If mycell.Comment.Visible = False Then
  Has_Comment = False
 Else
  Has_Comment = True
 End If
 
End Function

...with the call to that function being

=NOT(ISERROR(has_comment(E2)))

But the problem I was having was that if I inserted a comment into a Member name cell - the program didn't detect the change and automatically update it.

I tried changing the code to...

Function Has_Comment(mycell As Object) As Boolean

 Application.Volatile

 If mycell.Comment.Visible = False Then
  Has_Comment = False
 Else
  Has_Comment = True
 End If
 
End Function

...but this didn't seem to do anything for me. In both cases - the value in the Comment cell would change from TRUE or FALSE only after I inserted a comment, went to the cell, and then pressed Enter.

I'm fine with trying to get this "work around" to work if there is a way to make the macro work like the rest of sheet - updating whenever anything gets changed, but ultimately i'd be more happy if there was just a simple conditional statement that checked my above criteria and effectively count the number of flags were issued per each rank.

As is probably very obvious - my background in VB is basically non-existent - so I'm going to need a step by step walk through on what I should be doing to get this to work.

I saved a non-Macro enabled worksheet before attempting any Macro related things so if starting from square one is a recommended thing - I can do that :D
(since I probably screwed a lot of things up having never worked with the VBE before)

I'd also really appreciate information and descriptions about what i'm doing throughout this process since I am going to school for programming - I'm just in the very early stages :/ .

Anyways, I thank all of you who take the time out of your day to assist me in this well in advance!

Thank You,
DragonRulerX

I'm all for adjusting my method to not use the comment as "actionable data", but I am curious about what you said here...

A possibility would be to add a macro that uses the OnTime event to force a sheet calculation every x seconds. Or use the SelectionChange event. Not great solutions, but they could work.

What would be the drawbacks of using the OnTime and/or SelectionChange event?

From a quick Google search I found:
1) https://msdn.microsoft.com/en-us/lib.../ff196165.aspx - OnTime
2) https://msdn.microsoft.com/en-us/lib.../ff194470.aspx - SelectionChange

From my understanding - using the OnTime command and setting it to run every 1-second could be an option? Or is this too taxing on the computer/program?
Additionally - if this is a viable option - how would I approach using it?

The SelectionChange code confuses me a little bit...

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
 With ActiveWindow 
 .ScrollRow = Target.Row 
 .ScrollColumn = Target.Column 
 End With 
End Sub

I'm not sure I'd understand how this works exactly? Is this limited to a single cell or can it be applied to a range of cells? Also, how does it identify adding/removing a comment to a cell?

If we could make either of these work it'd be a very nice learning experience, but if you feel most of these won't work very well or consistently - I can always just add a column to the table for the comments (shrinking that column size to 0 pixels) and use a conditional checking to see if that cell is empty and "If Empty - No Comment | Else - Has Comment".