How to trigger a macro when the last value in a column contains the trigger word

[JohnGow383]

Hi All,

I would like to create an event to trigger a macro to display a comment in a particular cell when the lowest value in a range in another column is detected. When the column has another word added below it, to then delete the comment.

ie Trigger column range is M4:M53
Trigger word is "SOP" (I have a few trigger words for different comments in different cells but just stating one to get an idea) SOP has to be the lowest populated cell in the column
This is to add a comment into cell D22
When another word is entered below SOP, to then delete the comment in D22

Any help appreciated ty

 

[Alex Blakenburg]

Given that I now have the text you want to use and it looks pretty consistent and only needs the identifying Register Type added in 2 places, so I have made some changes.
I have moved the add comment and format it bold to a seperate Sub.
I also added a line feed after the end of the first sentence (chr10)

In your worksheet_change module replace the 2nd Select Case section (after the delete comments) with this.

            Dim cmtCell As Range
            Select Case Target.Value
                Case "SOP"
                    Set cmtCell = Range("D22")
                    Call AddAndFmtComment(rCell:=cmtCell, RegType:=Target.Value)
                Case "ROP"
                    Set cmtCell = Range("D23")
                    Call AddAndFmtComment(rCell:=cmtCell, RegType:=Target.Value)
                Case "SOP2"
                    Set cmtCell = Range("D25")
                    Call AddAndFmtComment(rCell:=cmtCell, RegType:=Target.Value)
                Case "ROP2"
                    Set cmtCell = Range("D26")
                    Call AddAndFmtComment(rCell:=cmtCell, RegType:=Target.Value)
                Case Else
                 ' Comment already deleted as initialisation step
            End Select

Then after End Sub of that module paste this, it is called by the original sub

Private Sub AddAndFmtComment(rCell As Range, RegType As String)
    Dim cmtText As String
    Dim cmtBoldTo As Long
   
    cmtText = RegType & " M/T Counter. " & Chr(10) & "Please add the " & _
                    RegType & " Counter here as well as cell C4"
    rCell.AddComment (cmtText)

    cmtBoldTo = InStr(rCell.Comment.Text, ".")
   
    With rCell.Comment.Shape.TextFrame
        With .Characters(1, cmtBoldTo).Font
            .Bold = True
        End With
    End With
End Sub

 

[JohnGow383]

Given that I now have the text you want to use and it looks pretty consistent and only needs the identifying Register Type added in 2 places, so I have made some changes.
I have moved the add comment and format it bold to a seperate Sub.
I also added a line feed after the end of the first sentence (chr10)

In your worksheet_change module replace the 2nd Select Case section (after the delete comments) with this.

            Dim cmtCell As Range
            Select Case Target.Value
                Case "SOP"
                    Set cmtCell = Range("D22")
                    Call AddAndFmtComment(rCell:=cmtCell, RegType:=Target.Value)
                Case "ROP"
                    Set cmtCell = Range("D23")
                    Call AddAndFmtComment(rCell:=cmtCell, RegType:=Target.Value)
                Case "SOP2"
                    Set cmtCell = Range("D25")
                    Call AddAndFmtComment(rCell:=cmtCell, RegType:=Target.Value)
                Case "ROP2"
                    Set cmtCell = Range("D26")
                    Call AddAndFmtComment(rCell:=cmtCell, RegType:=Target.Value)
                Case Else
                 ' Comment already deleted as initialisation step
            End Select

Then after End Sub of that module paste this, it is called by the original sub

Private Sub AddAndFmtComment(rCell As Range, RegType As String)
    Dim cmtText As String
    Dim cmtBoldTo As Long
   
    cmtText = RegType & " M/T Counter. " & Chr(10) & "Please add the " & _
                    RegType & " Counter here as well as cell C4"
    rCell.AddComment (cmtText)

    cmtBoldTo = InStr(rCell.Comment.Text, ".")
   
    With rCell.Comment.Shape.TextFrame
        With .Characters(1, cmtBoldTo).Font
            .Bold = True
        End With
    End With
End Sub

Wow. That worked perfectly. I am in total awe of your VBA knowledge. I find it very difficult to understand if I'm honest. Thanks very much for helping me with my Noon log sheet. Your assitance is greatly appreciated.

 

[Alex Blakenburg]

Glad I could help.
Working with comments in VBA is not that common and the comment bolding I had to Google too but the rest is fairly standard.
If you are not familiar with select case statements, definitely Google that.