Consulting | Excel Examples

Highlighting and Exporting Cell Notes

If you just want to print a cell note, you can use the File, Page Setup, Sheet, Print dialog and set Print Notes to ON. Excel will print the row and column heading for each note if you choose option Print Row and Column Headings in the Sheet dialog.

If you want to highlight the cells with notes so that you can tell which cells in your printed report contains notes, you can use this VBA procedure:

'\ select the range of cells containing notes before running this macro
Sub PrintBorderNotes()

Dim rng As Range
Dim rngSelect As Range
Set rngSelect = Selection
For Each rng In rngSelect.Cells


If Len(rng.NoteText) > 0 Then

rng.BorderAround LineStyle:=xlContinuous, ColorIndex:=3, Weight:=xlThick

End If


Next rng
ActiveSheet.PrintPreview
For Each rng In rngSelect.Cells

rng.Borders(xlLeft).LineStyle = xlNone
rng.Borders(xlRight).LineStyle = xlNone
rng.Borders(xlTop).LineStyle = xlNone
rng.Borders(xlBottom).LineStyle = xlNone
rng.BorderAround LineStyle:=xlNone
rng.BorderAround LineStyle:=xlNone

Next rng

End Sub

 If you need to export the contents of your notes, this VBA procedure will copy them to the cells in a new worksheet:

'\ select the range of cells containing notes before running this macro
Sub CopyBorderNotes()

Dim rng As Range
Dim rngSelect As Range
Set rngSelect = Selection
Worksheets.Add
For Each rng In rngSelect.Cells

If Len(rng.NoteText) > 0 Then
ActiveCell.Value = rng.NoteText
ActiveCell.Offset(0, 1).Value = rng.Address
ActiveCell.Offset(1, 0).Select
End If

Next rng

End Sub

Last modified: May 21, 1996