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

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview of Add comment to cell using VBA


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.

Insert Module to write code

  • 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.

VBA code to add comment to single cell

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.

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 cell D5.

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.

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

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

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

Comment on range of cells


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.

Dataset to add comment based on cell value

  • 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.

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 comment to those cells in column D that are greater than 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 comment to cells in column D.

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.

Comment when condition not met

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

Comment when condition met


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.

Existing comments to edit

  • 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.

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 attempts to modify the existing text of a comment associated with a specific cell in the worksheet with the given one.

As you can see, the comment has been changed.

Updated comments

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.

Existing comments to delete

  • 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.

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 that exist on the range D5:D10.

This is what our dataset becomes after deletion of comments.

Dataset after deleting 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.

Dataset to check the existence of 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.

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 with the color green.

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

This section colors the cells if there is any comment. This time it colors the cell with the color Red.

As per code, the cells are colored accordingly.

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. 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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mehedi Hassan
Mehedi Hassan

Mehedi Hassan, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, functions as an Excel & VBA Content Developer at ExcelDemy. His deep interest in research and innovation aligns seamlessly with his fervor for Excel. In this role, Mehedi not only skillfully addresses challenging issues but also exhibits enthusiasm and expertise in gracefully navigating intricate situations, emphasizing his unwavering dedication to consistently delivering outstanding content. His interests are Advanced... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo