My objective is to show the contents of a formulas cell that is looking up a list of descriptions to match item codes (around 500) in a Pivot table (the existing table descriptions were free text and create too many rows for the each item code). The look up item list is using a new set of standard descriptions from another query table. The descriptions can be very long and I want to keep the description column narrow by only showing the description if the cell if column A is selected.
I have VBA code to show the contents of the formula cell when selected. This works okay for the column A target. However, the comments are created and don't delete when I move out of column A and another cell is selected. Can this code be modified to delete the comment when any other cell (restrict to Column A) is selected in the worksheet.
Private LastTarget As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
On Error Resume Next
If Not LastTarget Is Nothing Then
If Not LastTarget.Comment Is Nothing Then LastTarget.Comment.Delete
If Not Trim$(Target.Value) = vbNullString Then
If Target.Comment Is Nothing Then
Target.Comment.Visible = True
Target.Comment.Shape.Width = 300 'Change as needed
Target.Comment.Shape.Height = 50 'Change as needed
Target.Comment.Shape.Fill.Transparency = 0# 'Make the comment a little see through