XL 2010 compiler cellule dans un commentaire

Sub MajCommentaires()

Dim I As Long, DerniereLigne As Long
   
    With Sheets("bilan")
         DerniereLigne = .Cells(.Rows.Count, 1).End(xlUp).Row
         For I = 1 To DerniereLigne
             With .Cells(I, 1)
                  If Not .Comment Is Nothing Then
                         .Comment.Delete
                         .AddComment Text:=InfoCommentaire(.Value)
                  Else
                         .AddComment Text:=InfoCommentaire(.Value)
                  End If
                  .Comment.Visible = False
             End With
         Next I
    End With

End Sub


Function InfoCommentaire(ByVal Reference As String) As String

Dim I As Long, DerniereLigne As Long

    InfoCommentaire = ""
   
    With Sheets("Prix")
         DerniereLigne = .Cells(.Rows.Count, 1).End(xlUp).Row
         For I = 1 To DerniereLigne
             If .Cells(I, 1) = Reference Then
                InfoCommentaire = .Cells(I, 2) & " " & .Cells(I, 3) & " € " & .Cells(I, 4) & Chr(10)
             End If
         Next I
    End With
   
    With Sheets("Geo")
         DerniereLigne = .Cells(.Rows.Count, 1).End(xlUp).Row
         For I = 1 To DerniereLigne
             If .Cells(I, 1) = Reference Then
                InfoCommentaire = InfoCommentaire & .Cells(I, 2)
             End If
         Next I
    End With
   

End Function

Sub CommBilan()
Dim shBil As Worksheet, shpri As Worksheet, shgeo As Worksheet, x As Range, np&, ng&, xcomm
   Set shBil = Worksheets("bilan"): Set shpri = Worksheets("prix"): Set shgeo = Worksheets("geo")
   Application.ScreenUpdating = False
   With shBil
      For Each x In Intersect(.Columns("a"), .UsedRange)
         xcomm = ""
         If x.Row <> 1 Then
            If Len(x) = 0 Then
               If Not x.Comment Is Nothing Then x.Comment.Delete
            Else
               np = Application.IfError(Application.Match(x, shpri.Columns(1), 0), -1)
               ng = Application.IfError(Application.Match(x, shgeo.Columns(1), 0), -1)
               If np > 1 Then
                  xcomm = shpri.Cells(np, 2) & " / " & shpri.Cells(np, 3).Text
                  xcomm = xcomm & " / " & shpri.Cells(np, 4)
               End If
               If ng > 1 Then xcomm = xcomm & vbLf & shgeo.Cells(ng, 2)
               If x.Comment Is Nothing Then x.AddComment
               x.Comment.Visible = False: x.Comment.Text Text:=xcomm
            End If
         End If
      Next x
   End With
End Sub