How to highlight Excel comment using VBA

by | Nov 6, 2018 | Excel Macros, Excel Tips & Tricks

How to highlight Excel comment using VBA

Finding all comment fields inside a workbook might be a challenge if you're not sure where they might be hiding. Let us show you how to highlight Excel comment using VBA to find them quickly!

 

How to highlight Excel comment using VBA

VBA has some predefined types for cells that contains special values. These types can be accessed using the SpecialCells method. The SpecialCells method uses two arguments to specify the cells; Type and Value. Using a combination these two arguments, you can easily access the cell or range type you want. Below are lists for values that both arguments can get:

Type

  • xlCellTypeAllFormatConditions. Cells of any format
  • xlCellTypeAllValidation. Cells having validation criteria
  • xlCellTypeBlanks. Empty cells
  • xlCellTypeComments. Cells containing notes
  • xlCellTypeConstants. Cells containing constants
  • xlCellTypeFormulas. Cells containing formulas
  • xlCellTypeLastCell. The last cell in the used range. Note this will include empty cells that have had any of cells default format changed.
  • xlCellTypeSameFormatConditions. Cells having the same format
  • xlCellTypeSameValidation. Cells having the same validation criteria
  • xlCellTypeVisible. All visible cells

Value

  • xlErrors
  • xlLogical
  • xlNumbers
  • xlTextValues

To find all cells with comments, we can use xlCellTypeComments value for Type argument. On the other hand, the Value argument can be omitted to access all cells with comments.

After you get the range, you can use ColorIndex property to set that range a color. Here are some index numbers for colors:

  • 3: Red
  • 4: Green
  • 5: Blue
  • 6: Yellow
  • 0: No Fill

Before continuing the codes here is a simple explanation about them. You can use codes in two ways:

  • Module
  • Immediate Window

In the Module method, you need to add the module into the workbook or the add-in file. Copy and paste the code into the module to run it. The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. Remember to save your file in either XLSM or XLAM format to save your VBA code.

The Immediate Window method, on the other hand, is essentially a quick and dirty method where you can simply copy and paste the code into the Immediate Window and press the Enter key to run it. Unfortunately, any code you use in the Immediate Window will not be saved. Also note that icons and keyboard shortcuts will not be available.

 

Highlight cells with comments in active worksheet

Module method:

Sub HighlightCellsWithCommentsInActiveWorksheet()

   ActiveSheet.UsedRange.SpecialCells(xlCellTypeComments).Interior.ColorIndex = 4

End Sub

Immediate Window method:

ActiveSheet.UsedRange.SpecialCells(xlCellTypeComments).Interior.ColorIndex = 4

 

Highlight cells with comments in all worksheets

Module method:

Sub HighlightCellsWithCommentsInAllWorksheets()

   Dim ws As Worksheet

   For Each ws In ActiveWorkbook.Worksheets

      ws.UsedRange.SpecialCells(xlCellTypeComments).Interior.ColorIndex = 4

   Next ws

End Sub

Immediate Window method:

For each ws In ActiveWorkbook.Worksheets: ws.UsedRange.SpecialCells(xlCellTypeComments).Interior.ColorIndex = 6: Next ws