Add coments VBA

If cell = "o" Then
 With cell
  .AddComment
  .Comment.Text Text:="o"
  .Comment.Visible = True
 End With
End If

If there are comments already you may for instance override adding by

If cell = "o" Then
 With cell
  On Error Resume Next
  .AddComment
  On Error Goto 0
  .Comment.Text Text:="o"
  .Comment.Visible = True
 End With
End If

I've shown only the middle part to be inserted in your code. The whole sub could read:

Sub EjemploComentario()

Dim rg As Range, cell As Range

rg = Sheets("Leavetracker").range("B8:NH27")

For Each cell In rg

 If cell = "o" Then
  With cell
   On Error Resume Next
   .AddComment
   On Error Goto 0
   .Comment.Text Text:="o"
   .Comment.Visible = True
  End With
 End If
Next cell

End Sub

Check if the sheetname is correct then try this way.

Set rg = Sheets("Leavetracker").range("B8:NH27")

Yes, it could.

But observing problems with the implementation - I'd strongly recommend following the hint from a yellow banner above.



Anyway, let's try. The code for the Change event handler in a sheet (right click on the sheet name tab and select view code):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If not intersect(target,range("B8:NH27")) is nothing then
  For Each cell In intersect(target,range("B8:NH27"))
    If cell = "o" Then
      With cell
        On Error Resume Next
        .AddComment
        On Error Goto 0
        .Comment.Text Text:="o"
        .Comment.Visible = True
      End With
' may be here Else section to clear comment if there is no "o" in the cell
    End If
  Next cell
End if
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B8:NH27")) Is Nothing Then
    If Target = "o" Then
        With Target
            On Error Resume Next
            .AddComment
            On Error GoTo 0
            With .Comment
                .Text Text:="o"
                .Visible = True
            End With
        End With
      ElseIf Target = vbNullString Then
        Target.Comment.Delete
      End If
End If
End Sub

You're welcome and thanks for rep+.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.