Modifiy

[mayoung]

I accidentally submitted this with wrong subject. I wanted it to say Modify Comment. I tried to modify or delete and start over but do know how to do either.

But ...

How can this be modified so it works with a Range or Activecell. I have tried several combinations without success.

Sub ModifiyComments()
Dim MyComments As Comment
Dim LArea As Long
For Each MyComments In ActiveSheet.Comments
With MyComments
.Shape.AutoShapeType = msoShapeRoundedRectangle
.Shape.TextFrame.Characters.Font.Name = "Tahoma"
.Shape.TextFrame.Characters.Font.Size = 8
.Shape.TextFrame.Characters.Font.ColorIndex = 2
.Shape.Line.ForeColor.RGB = RGB(0, 0, 0)
.Shape.Line.BackColor.RGB = RGB(255, 255, 255)
.Shape.Fill.Visible = msoTrue
.Shape.Fill.ForeColor.RGB = RGB(58, 82, 184)
.Shape.Fill.OneColorGradient msoGradientDiagonalUp, 1, 0.23
End With
Next 'comment
End Sub

 

[Fluff]

Do you have a question?

 

[mayoung]

Do you have a question?

This submitted before I was ready sorry.. Please see edited

 

[Fluff]

Do you just want to change the comment for one cell, or a range of cells?

 

[mayoung]

If the the Range could be adjusted to do one cell like Range(“C2”) or do a group though adjusting the range Range(”C2:E10) etc

 

[Fluff]

How about

Sub ModifiyComments()
Dim Cl As Range
For Each Cl In Range("C2:E10")
   If Not Cl.Comment Is Nothing Then
      With Cl.Comment
         .Shape.AutoShapeType = msoShapeRoundedRectangle
         .Shape.TextFrame.Characters.Font.Name = "Tahoma"
         .Shape.TextFrame.Characters.Font.Size = 8
         .Shape.TextFrame.Characters.Font.ColorIndex = 2
         .Shape.Line.ForeColor.RGB = RGB(0, 0, 0)
         .Shape.Line.BackColor.RGB = RGB(255, 255, 255)
         .Shape.Fill.Visible = msoTrue
         .Shape.Fill.ForeColor.RGB = RGB(58, 82, 184)
         .Shape.Fill.OneColorGradient msoGradientDiagonalUp, 1, 0.23
      End With
   End If
Next 'comment
End Sub

 

[mayoung]

Worked Perfect Thank You so much.

 

[Fluff]

You're welcome & thanks for the feedback