Automatically updating comments across worksheets

[svk01]

Hello, I am using VBA code extracted from Andrew Poulsom's prior post. My version is pasted below:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With Worksheets("HeatMap - Strengths+Weaknesses")
Select Case Target.Address
Case "$F$2"
.Range("F2").Comment.Text Text:=Target.Value
Case "$F$3"
.Range("F3").Comment.Text Text:=Target.Value
End Select
End With
End Sub

However, when I try to run this code I continuously receive an "argument not optional" error and despite many hours of debugging I cannot figure out how to fix it. For your reference, the below code has been attached to a sheet called "All Responses." This sheet contains data that has been copied into comments placed in a second sheet (within the same workbook) called "HeatMap - Strengths+Weaknesses." As you can probably tell, the locations of the originating cells in "All Responses" mirrors those in "HeatMap - Strengths+Weaknesses" because the aim here is to use the comment to elaborate on a score given to each response in "All Responses." Do you have any idea why the compiler is not receiving an argument and is thus outputting this error? By the way, I am unaware of the exact code posing problems because it doesn't highlight any code when the error is outputted.

 

[svk01]

Yessss @Fluff, you wizard, thank you!!! That finally worked! Is there any way per chance to delete the old comment too?

 

[Fluff]

Use this instead

.Range("F2").CommentThreaded.Text Target.Value

 

[svk01]

THANK YOU SO MUCH!!! I am eternally grateful.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[svk01]

One last question if you don't mind - is it possible to set this code for a range of cells? i.e. to set it such that F2:F36 in one sheet maps to comments in F2:F36 in the other sheet?

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("F2:F36")) Is Nothing Then
      With Worksheets("HeatMap - Strengths+Weaknesses")
         .Range(Target.Address).CommentThreaded.Text Target.Value
      End With
   End If
End Sub

This assumes you will only change one cell at a time.

 

[svk01]

@Fluff you are a hero. Thank you!!!!

 

[Fluff]

My pleasure.