This is an overview.
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.
- 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.
The code added a comment to the cell. Hover your cursor over the cell to see the comment.
Range("D5").AddComment ("Need to engage more clients")This will print the comment Need to engage more clients in D5.
Read More: How to Add Floating Comment in Excel
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.
Code Breakdown
For i = 5 To 10
   Cells(i, 4).AddComment "Need to engage more clients"
Next iinitiates 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.
Example 3 – Add a Dynamic Comment Based on the 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.
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 IfIf 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.
In C6, the comment is Great Job.
How to Edit a Cell Comment Using Excel VBA
To change existing comments, use the .Comment.Text property.
To change: Need to engage more clients:
- 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.
Code Breakdown
For i = 5 To 10
   Cells(i, 4).Comment.Text "Need to work harder"
Next iInside 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.
Read More: Creating and Editing Comments in Excel
How to Remove Comment from Cell Using Excel VBA
To remove comments, use the .Comment.Delete property.
- 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.
Code Breakdown
For i = 5 To 10
   Cells(i, 4).Comment.Delete
Next iInside the loop, the code deletes any comments in D5:D10.
This is the output.
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.
- 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.
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 iThis section colors the cells if there is a comment. It colors the cell Red.
This is the output.
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.
Related Articles
<< Go Back to How to Add a Comment in Excel | Comments in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
