Selected Answer
Sorry, I was a bit slow here but I finally got it. Below is the code that does the job. It must be installed in the code sheet of the 'Monitors' worksheet. You can simply copy and paste either from here or from the code sheet in the attached workbook.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' 17 Jul 2018
' change as required
Const TargetSheet As String = "Productivity"
Dim Rng As Range
Dim Rl As Long, Cl As Long ' last row / column
Dim Arr As Variant
Dim C As Long
Dim Txt As String
' find the last used row in column A
Rl = Cells(Rows.Count, "A").End(xlUp).Row
' find the last used column in row 1
Cl = Cells(1, Columns.Count).End(xlToLeft).Column
' changes above row 2 and to the left of column 2 (B) are ignored
Set Rng = Range(Cells(2, 2), Cells(Rl, Cl))
If Not Application.Intersect(Target, Rng) Is Nothing Then
Set Rng = Range(Cells(Target.Row, "A"), Cells(Target.Row, Cl))
Arr = Rng.Value
For C = 2 To UBound(Arr, 2)
If Len(Arr(1, C)) Then
If Len(Txt) Then Txt = Txt & Chr(10)
Txt = Txt & Arr(1, C)
End If
Next C
With Worksheets(TargetSheet)
' look for list of names in column B
Rl = .Cells(.Rows.Count, "B").End(xlUp).Row
Set Rng = .Range(.Cells(1, "B"), .Cells(Rl, "B"))
On Error Resume Next
C = Application.Match(Arr(1, 1), Rng, 1)
If Err Then
MsgBox "The agent's name " & Arr(1, 1) & " wasn't" & vbCr & _
"found on the '" & TargetSheet & "' tab." & vbCr & _
"No comment could be added.", _
vbInformation, "Missing agent's name"
Else
' write the comment to column R
SetComment Worksheets(TargetSheet).Cells(C, "R"), Txt
End If
End With
End If
End Sub
Sub SetComment(Cell As Range, _
Optional ByVal Txt As String, _
Optional ByVal Concat As Boolean)
' 12 Jul 2018
' deletes existing comment if Txt = "" and Concat = False
Dim Cmt As String
With Cell
On Error Resume Next
Cmt = .Comment.Text
.Comment.Delete
On Error GoTo 0
If Concat Then
If Len(Cmt) Then Cmt = Cmt & Chr(10)
Else
Cmt = ""
End If
Txt = Cmt & Txt
If Len(Txt) Then .AddComment Cmt & Txt
End With
End Sub
The attached workbook is fully functioning. Make any change on the 'Monitors' tab (otehr than in the column captions or column A) and the summary is written to a comment on the 'Productivity' tab. Any existing comment is replaced. If there are no Monitor Scores there will be no comment. The comment in R2 was created by the above code.
I have added some comments to the code which should enable you to make simple changes. Note that since the workbook now contains code it must be saved in XLSM format.