When a user enters a comment in a cell, and you would like to flag that cell in a different color to make it stand out as a commented cell, here's how.
I initially wrote this because users were having trouble distinguishing which cells had comments. When a user enters a comment (RightClick > Insert Comment) in a cell, it will highlight the cell in a light shade of green to make it stand out.
The only thing I didn't include is a way to remove the highlight if a comment is deleted. Although, that could be easily implemented.
This code should be pasted in the "ThisWorkbook" object in your VBA editor:
Code
Public PrevCommentCount As Integer
Public CurrCommentCount As Integer
Dim CWSName As String
Dim PWSName As String
'***When workbook opens, save comment count
Private Sub Workbook_Activate()
CurrCommentCount = ActiveSheet.Comments.Count
End Sub
'***When workbook is open, init variables
Private Sub Workbook_Open()
'Ensure comments display indicator
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
'Get current comment count
CurrCommentCount = ActiveSheet.Comments.Count
End Sub
'***Event fires when sheets are activated
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Flag = False Then
CWSName = Sh.Name
CurrCommentCount = Sh.Comments.Count
End If
End Sub
'***Event is fired when sheets are deactivated
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
PWSName = Sh.Name
PrevCommentCount = Sh.Comments.Count
If PrevCommentCount <> CurrCommentCount And PWSName = CWSName Then
For Each mycomment In Worksheets(Sh.Name).Comments
Worksheets(ChartSheet).Range(mycomment.Parent.Address).Interior.ColorIndex = 35
Worksheets(ChartSheet).Range(mycomment.Parent.Address).Font.ColorIndex = 1
Worksheets(ChartSheet).Range(mycomment.Parent.Address).Font.Bold = True
Next mycomment
End If
End Sub
'***When new sheet is selected, EVENT fires
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Comments.Count <> CurrCommentCount Then
'Color all comments green
For Each mycomment In Worksheets(Sh.Name).Comments
Worksheets(Sh.Name).Range(mycomment.Parent.Address).Interior.ColorIndex = 35
Worksheets(Sh.Name).Range(mycomment.Parent.Address).Font.ColorIndex = 1
Worksheets(Sh.Name).Range(mycomment.Parent.Address).Font.Bold = True
Next mycomment
CurrCommentCount = Sh.Comments.Count
End If
End Sub