How to Use Excel VBA to Add Comment to Cell (3 Examples)

In this article, you will learn how use Excel VBA to add comment to cell with VBA macro. We will demonstrate 3 examples to add comment to single cell, to multiple cells and assign comments based on cell value.

Comments serve as a form of documentation, documenting the logic, assumptions, or sources of data used in a cell. Comments facilitate collaboration among multiple users working on the same workbook. Users can leave comments to ask questions, provide feedback, or communicate important information about specific cells.

Introduction to VBA Range.AddComment Method

.AddComment method is applied to Range or Cells objects to assign text-based comments to specific ranges or cells. The comments provide additional information regarding the content of the cells. It creates a comment box associated with the specified range or cell, visible when the user hovers over or selects the cell.

Range(“A1”).AddComment (“You can add comment here”)

Add Comment to Cell with Excel VBA: 3 Suitable Examples

In this article, we will see 3 examples to add comments to cells using a VBA script. If you are new to Microsoft Visual Basic Application, follow this article on How to Write VBA Code in Excel.

We will see how to add comments to any single cell, to multiple cells, and how you can add different comments based on cell values. Obviously, we will use the Range.AddComment property. We will try to give simple explanations for each code so that you can use this property from now on. So, let’s get to the main part of the article.

1. Add Comment to Any Cell

If you want to add a comment to any particular cell, just follow these simple steps.

• First, create a Module in the Visual Basic Editor.

• Then write/paste this code into the Module.

Sub addcommenttocell()
    'This will add comment to cell D5
    Range("D5").AddComment ("Need to engage more clients")
End Sub

• Now, press F5 or go to Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.

You can see, the code added a comment on the designated cell. Hover your cursor over the cell and you will be able to see the comment.

Range("D5").AddComment ("Need to engage more clients")

Read More: How to Add Floating Comment in Excel

2. Add Comment to Multiple Cells

To add the same comment to a range of cells, just follow these steps.

• First, create a Module in the Visual Basic Editor.
• Then write/paste this code into the Module.

Sub addcommenttorange()
For i = 5 To 10
    'This will add comment to range D5:D10
    Cells(i, 4).AddComment "Need to engage more clients"
Next i
End Sub

• Now, press F5 or go to Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.

For i = 5 To 10
    Cells(i, 4).AddComment "Need to engage more clients"
Next i

As you hover your cursor to each cell, you will see the comment has been to each one.

3. Add Dynamic Comment Based on Cell Value

In this section, we will add comments based on the cell value. We have a dataset for a set of employees from whom some managed to achieve sales targets and some didn’t. For those who achieved their assigned target we will add this comment, Great Job and for the employees who lagged behind we will use this comment, Need to engage more clients for them.

• First, create a Module in the Visual Basic Editor.
• Then write/paste this code into the Module.

Sub conditionalcomment()
    For i = 5 To 15
        ' Check if the value in column D is greater than
        'the value in column C for the current row.
        If Cells(i, 4) > Cells(i, 3) Then
            ' If the condition is true, add comment "Great Job" to cell
            Cells(i, 4).AddComment "Great Job"
        Else
            ' If the condition is false, add the comment to cell
            Cells(i, 4).AddComment "Need to engage more clients"
        End If
    Next i
End Sub

• Now, press F5 or go to Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.

If Cells(i, 4) > Cells(i, 3) Then
          Cells(i, 4).AddComment "Great Job"

Else
     Cells(i, 4).AddComment "Need to engage more clients"
End If

When you run the code, you will see in cell D5, the comment is Need to engage more clients as the employee didn’t meet expectations.

And in cell C6, the comment is Great Job as this employee passed his assigned target sales.

How to Edit Cell Comment Using Excel VBA

Suppose, you want to change existing comments. You can use .Comment.Text property to replace an existing comment with a new one.

Here, in our dataset, we had a previously set comment Need to engage more clients. We want to change that.

• First, create a Module in the Visual Basic Editor.
• Then write/paste this code into the Module.

Sub Editcomment()
For i = 5 To 10
    'This line of code will replace the existing text
    'of the comment in column D for rows 5 to 10
    Cells(i, 4).Comment.Text "Need to work harder"
Next i
End Sub

• Now, press F5 or go to Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.

For i = 5 To 10
    Cells(i, 4).Comment.Text "Need to work harder"
Next i

As you can see, the comment has been changed.

Read More: Creating and Editing Comments in Excel

How to Remove Comment from Cell Using Excel VBA

If you want to remove comments from cells you will have to use .Comment.Delete property.

Here, in our dataset, we had a previously set comment Need to engage more clients. We want to delete that.

• First, create a Module in the Visual Basic Editor.
• Then write/paste this code into the Module.

Sub Deletecomment()
For i = 5 To 10
    'This line will delete existing comment on every cell
    Cells(i, 4).Comment.Delete
Next i
End Sub

• Now, press F5 or go to Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.

For i = 5 To 10
    Cells(i, 4).Comment.Delete
Next i

This is what our dataset becomes after deletion of comments.

How to Check If a Cell Contains Comment Using Excel VBA

Usually, a cell with a comment can be recognized with a subtle Red triangle in the right-upper corner of the cell. But if you need more recognizable facts, you can color them. In this section, we will differentiate cells with and without comments with VBA macro by coloring them.

In our dataset, cells D5, D7, and D9 contain comments, and cells D6, D8, and D10 don’t. So we will give the color red to cells with comments and green to those without comments.

• First, create a Module in the Visual Basic Editor.
• Then write/paste this code into the Module.

Sub checkcomment()
    For i = 5 To 10
        ' Check if a comment exists for the current cell in column D.
        If Cells(i, 4).Comment Is Nothing Then
            ' If no comment exists, change the interior color of the cell to green.
            Cells(i, 4).Interior.Color = RGB(0, 255, 0)
        Else
            ' If a comment exists, change the interior color of the cell to red.
            Cells(i, 4).Interior.Color = RGB(255, 0, 0)
        End If
    Next i
End Sub

• Now, press F5 or go to Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.

For i = 5 To 10
    If Cells(i, 4).Comment Is Nothing Then
       Cells(i, 4).Interior.Color = RGB(0, 255, 0)

    Else
          Cells(i, 4).Interior.Color = RGB(255, 0, 0)
    End If
Next i

As per code, the cells are colored accordingly.

Frequently Asked Questions

1. Can I format the comment text (e.g., change font, size, color) using VBA?

Ans: No, you cannot directly format the comment text (font, size, color) using VBA. Excel does not provide native functionality to format comment text through VBA.

2. How can I protect or lock comments from being modified using VBA?

Ans: To protect or lock comments from being modified using VBA, you can set the Locked property of the comment to True, and then protect the worksheet. This prevents users from editing or deleting the comments.

3. Can I add hyperlinks within comments using VBA?

Ans: No, it is not possible to directly add hyperlinks within comments using VBA. Excel’s comment functionality does not support the inclusion of hyperlinks. However, you can add hyperlinks to cells themselves, which can be accessed by users when viewing the comments.

Download Practice Workbook
Download this file to practice with the article.

Conclusion

If you add comments to your cell, they give certain context about the type of your data. With the knowledge gained from this guide, you can use VBA’s capabilities to add, modify, and manage comments effectively, making your Excel worksheets more informative and user-friendly. Using VBA to add comment to cell saves not only time but also gives users the efficiency to clarify data and presentation.

Related Articles

• How to Format Comments in Excel
• How to Add Comment in Excel Formula
• How to Insert Picture in Excel Comment
• How to Reference Comments in Excel

<< Go Back to How to Add a Comment in Excel | Comments in Excel | Learn Excel