Modifiy

mayoung

Board Regular
Joined
Mar 26, 2014
Messages
95
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.





VBA Code:
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
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,019
Office Version
365
Platform
Windows
Do you just want to change the comment for one cell, or a range of cells?
 

mayoung

Board Regular
Joined
Mar 26, 2014
Messages
95
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,019
Office Version
365
Platform
Windows
How about
VBA Code:
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,019
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,081,640
Messages
5,360,196
Members
400,576
Latest member
ng999

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top