How to Use Excel VBA to Add a Comment to a Cell - 3 Examples

This is an overview.

Overview of Add comment to cell using VBA

If you are new to Microsoft Visual Basic Application, read How to Write VBA Code in Excel.

Example 1 – Add Comment to Any Cell

Create a Module in the Visual Basic Editor.

Insert Module to write code

Enter this code in the Module.

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

Press F5 or click Run Sub/Userform to run the code.

VBA code to add comment to single cell

The code added a comment to the cell. Hover your cursor over the cell to see the comment.

Comment on the designated cell

Note:

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

This will print the comment Need to engage more clients in D5.

Example 2 – Add Comment to Multiple Cells

To add the same comment to a range of cells:

Create a Module in the Visual Basic Editor.
Enter this code in 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

Press F5 or click Run Sub/Userform to run the code.

VBA code to add comment to range of cell

Code Breakdown

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

initiates a loop that will execute the code starting from D5 and ending at D10. Inside the loop, the line adds a comment to every cell in column D.

Hover your cursor over the cells to see the comment.

Comment on range of cells

Example 3 – Add a Dynamic Comment Based on the Cell Value

Dataset to add comment based on cell value

Create a Module in the Visual Basic Editor.
Enter this code in 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

Press F5 or click Run Sub/Userform to run the code.

VBA code add comment based on cell value

Code Breakdown

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

This line will add Great Job to cells in column D greater than the corresponding cells in column C.

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

If the cells in column D are less than the corresponding cells in column C, the code will add Need to engage more clients.

Run the code, and you will see the comment Need to engage more clients in D5.

Comment when condition not met

In C6, the comment is Great Job.

Comment when condition met

How to Edit a Cell Comment Using Excel VBA

To change: Need to engage more clients:

Existing comments to edit

Create a Module in the Visual Basic Editor.
Enter this code in 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

Press F5 or click Run Sub/Userform to run the code.

VBA code to edit cell comment

Code Breakdown

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

Inside the loop, the code replaces the existing text of a comment associated with a specific cell in the worksheet with the given text.

The comment changed.

Updated comments

How to Remove Comment from Cell Using Excel VBA

Existing comments to delete

Create a Module in the Visual Basic Editor.
Enter this code in 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

Press F5 or click Run Sub/Userform to run the code.

VBA code to delete comments

Code Breakdown

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

Inside the loop, the code deletes any comments in D5:D10.

This is the output.

Dataset after deleting comments

How to Check If a Cell Contains Comment Using Excel VBA

A cell with a comment has a subtle Red triangle in its right-upper corner of the cell.

You can differentiate cells with and without comments by coloring them.

D5, D7, and D9 contain comments. D6, D8, and D10 don’t. Use red in cells with comments and green in those without comments.

Dataset to check the existence of comments

Create a Module in the Visual Basic Editor.
Enter this code in 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

Press F5 or click Run Sub/Userform to run the code.

VBA code to check whether any comment exists

Code Breakdown

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

This section creates a loop to check if a cell in column D has comments. If it doesn’t find any comment, the code paints the cell green.

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

This section colors the cells if there is a comment. It colors the cell Red.

This is the output.

Confirmation of comments shown by color

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.

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

Ans: You can set the Locked property of the comment to True, and protect the worksheet.

3. Can I add hyperlinks within comments using VBA?

Ans: No, it is not possible to directly add hyperlinks within comments using VBA.

Download Practice Workbook
Download this file to practice.

Adding Comment to Cell.xlsm

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

Get FREE Advanced Excel Exercises with Solutions!

How to Use Excel VBA to Add a Comment to a Cell – 3 Examples

Example 1 – Add Comment to Any Cell

Example 2 – Add Comment to Multiple Cells

Example 3 – Add a Dynamic Comment Based on the Cell Value

How to Edit a Cell Comment Using Excel VBA

How to Remove Comment from Cell Using Excel VBA

How to Check If a Cell Contains Comment Using Excel VBA

Frequently Asked Questions

Related Articles

Leave a reply Cancel reply