Automatically Add Comments to Excel Cells with VBA

VBA & macros · Updated November 18, 2026

Yes. In desktop Excel, VBA can automatically add a note to a cell with Range.AddComment; in current Microsoft 365 versions, VBA can also add a threaded comment with Range.AddCommentThreaded. The example below adds notes from a neighboring column, and a worksheet event example shows how to add one when someone enters a value.

Comments and notes are different in modern Excel

Excel’s terminology can be confusing because the feature once called a comment was changed into a threaded conversation. In current Excel, a threaded comment supports replies and is intended for collaboration. The older, single-text annotations are now called notes. Existing workbooks may still contain notes created before the terminology changed.

The distinction matters in VBA. The established Range.AddComment method creates a note, not a threaded conversation. A note is a good fit for a macro that adds a fixed explanation, an instruction, or a value copied from another cell. If your requirement is specifically a conversation with replies, use Range.AddCommentThreaded in an Excel build that supports it, and check the workbook’s collaboration behavior before distributing the macro.

This article’s main examples use notes because they are straightforward to create, inspect, and update through the traditional VBA object model. In the Excel interface, notes are available from the Review tab; threaded comments are also managed there. Do not assume that a VBA macro using AddComment will create the modern threaded type just because the interface labels the feature “Comments.”

Add notes from a neighboring column

This macro reads explanatory text from column B and adds it as a note to the cell in column A on the same row. It starts at row 2, so row 1 can hold headings. It skips empty source cells and leaves existing notes unchanged. Change the sheet name and row limits to match your workbook.

Sub AddNotesFromColumnB()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim targetCell As Range
    Dim noteText As String

    ' Change the sheet name to match your workbook.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    For r = 2 To lastRow
        Set targetCell = ws.Cells(r, "A")
        noteText = Trim$(CStr(ws.Cells(r, "B").Value2))

        If Len(noteText) > 0 Then
            ' Do not replace a note that is already present.
            If targetCell.Comment Is Nothing Then
                targetCell.AddComment noteText
            End If
        End If
    Next r
End Sub

For example, if B2 contains “Confirm the supplier code” and A2 contains a product ID, running the macro adds that sentence as a note to A2. The same logic applies down the rows. The last row is found from column B, so a populated source cell in B is considered even if the corresponding A cell is blank; Excel can attach a note to a blank cell too.

The test for an existing note is intentional. If you want the macro to replace old notes instead, decide that explicitly rather than silently overwriting user content. A replacement version can delete the old note before calling AddComment, but deletion is a destructive change and should be tested on a copy.

Put the macro in a standard module and run it

In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11, choose Insert > Module, and paste the first macro into that module. Close or switch back to Excel, then choose Developer > Macros, select AddNotesFromColumnB, and choose Run. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer.

Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm). A standard .xlsx file cannot store VBA code. On a Mac, VBA is available in desktop Excel; the editor shortcut can vary by keyboard, so use Developer > Visual Basic if the shortcut does not work. Excel for the web can open a macro-enabled workbook but cannot run or edit its VBA macros.

Before running any macro, save a separate copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, including adding, changing, or deleting notes. Close the workbook without saving if you need to discard a test run and have not made other changes you want to keep.

Add a note automatically when a cell changes

A standard macro runs when you launch it. To respond whenever a user edits a cell, use the worksheet’s Worksheet_Change event instead. This example watches A2:A1000 and adds a note containing the entered value, but only if the cell does not already have a note. Paste this code into the code module for the specific worksheet, not into a standard module.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim changedCells As Range
    Dim cell As Range
    Dim eventsWereEnabled As Boolean

    Set changedCells = Intersect(Target, Me.Range("A2:A1000"))
    If changedCells Is Nothing Then Exit Sub

    eventsWereEnabled = Application.EnableEvents
    On Error GoTo CleanUp
    Application.EnableEvents = False

    For Each cell In changedCells.Cells
        If Len(CStr(cell.Value2)) > 0 Then
            If cell.Comment Is Nothing Then
                ' Store the entered value as a note.
                cell.AddComment "Entered value: " & CStr(cell.Value2)
            End If
        End If
    Next cell

CleanUp:
    Application.EnableEvents = eventsWereEnabled
End Sub

To install it, press Alt+F11, find the workbook in Project Explorer, and double-click the worksheet that should be monitored. Paste the event procedure into that worksheet’s code window. The Intersect check limits the event to the specified range, and handling each cell means pasting several values at once is supported. If a user edits a cell that already has a note, this version leaves it alone rather than replacing it.

Worksheet events run only when macros are enabled and the workbook is open in desktop Excel. They do not run in Excel for the web. The event procedure also does not run merely because a formula’s calculated result changes: Worksheet_Change responds to edits or pasted values, not recalculation. For formula results, a different event strategy may be needed, and it should be designed carefully to avoid repeated updates.

Choose the right note behavior

Before automating annotations, decide what should happen when the macro encounters an existing note, a blank source, or a multi-cell paste. These choices affect whether the process is safe to repeat and whether it preserves information entered by people.

  • Skip existing notes: safest when users may have added their own explanations. The sample macro uses this behavior.
  • Replace existing notes: useful when the source column is the authoritative text, but it removes previous note content. Test the replacement logic on a copy first.
  • Remove notes when the source is blank: only do this if a blank source definitively means the annotation should be deleted; otherwise, a temporarily empty cell could erase useful context.
  • Limit the target range: use a specific range such as A2:A1000 when you do not want headings or unrelated cells affected.
  • Define the note text: decide whether it comes from another cell, a fixed message, a timestamp, or a combination. Avoid including sensitive information that should not be visible to workbook users.

For notes created with VBA, the text can also include a line break using vbCrLf. For example, a note can put a status on one line and an instruction on the next. Long notes are less convenient to read, so a short summary in the note with detailed information stored in a table is often easier to maintain.

Common errors and how to fix them

  • “Subscript out of range”: the worksheet name in Worksheets("Sheet1") does not exactly match a tab name, or the sheet is not in ThisWorkbook. Check spelling and punctuation.
  • The macro appears to do nothing: confirm that the source column contains text below row 1, the macro is in a standard module, and macros are enabled. The sample deliberately skips blank source cells and existing notes.
  • The note is on the wrong cell: check the target and source column references. In the example, column B supplies the text and column A receives the note.
  • Run-time error on a protected sheet: protection can prevent changes to cells or notes. Unprotect the sheet using the workbook’s authorized process, or revise the protection setup; do not embed a password in a distributed macro without considering who can access it.
  • Event code stops responding: check that it is in the correct worksheet module and that events have not been left disabled by other VBA code. The sample restores the previous Application.EnableEvents state when it exits.

Other edge cases include merged cells, filtered lists, and very large pasted ranges. Test the macro against a small representative set first. If data is arranged as an Excel Table, use its actual columns and headers deliberately rather than assuming that adding rows will automatically change a hard-coded event range such as A2:A1000.

Notes, threaded comments, and alternatives

Use a note when the information is a static annotation attached to a cell, such as a definition or a data-entry reminder. Use a threaded comment when colleagues need to discuss an item and reply to one another. In supported Microsoft 365 desktop builds, the VBA method Range.AddCommentThreaded is intended for creating threaded comments; the note example above does not call it. Availability and collaboration behavior can vary with Excel version and account setup, so confirm the method in the VBA Object Browser for the installation where the macro will run.

If the real goal is to display a message only when someone selects a cell, Data Validation input messages may be a better fit than notes. They are configured through Data > Data Validation and do not create a conversation or a persistent annotation. If information needs to be searchable, sortable, or used in reports, put it in a dedicated worksheet column instead. Notes are visually attached to cells, but they are not a substitute for structured data.

Frequently Asked Questions

Can VBA add a comment to every used cell?
Yes, but define “used” carefully. A macro can loop over a chosen range or detect the last row and column, then add notes only where a condition is met. Start with a bounded range and test it; looping over every cell in a whole worksheet is needlessly broad for most tasks.

Does Range.AddComment create a threaded comment?
No. Range.AddComment creates a legacy-style note in current Excel terminology. For a threaded conversation, use Range.AddCommentThreaded where that method is supported.

Can I automatically add a comment when a value is entered?
Yes. Put a Worksheet_Change event procedure in the relevant worksheet’s code module, restrict it with Intersect, and decide how to handle existing notes and multi-cell pastes. The event example above adds a note for edits in A2:A1000.

Can Excel for the web run this VBA macro?
No. Excel for the web can work with many workbook features, but it does not run VBA macros. Open the macro-enabled workbook in desktop Excel to run or edit the VBA code.

Can I undo notes added by a macro with Ctrl+Z?
No. Excel’s normal Undo history does not undo VBA actions. Save a backup copy before running the macro, or write and test a separate cleanup macro if you need a controlled way to remove notes later.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.