Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Does cell have comment? (EXCEL 97/2000)

    Want to run some code that (e.g.) colors all comments in each (commented) cell blue. So... I started out doing something like:

    <font color=blue>For Each Cl in [MyRange]
    Cl.select</font color=blue>

    Then my first step would be to test if the activecell would even HAVE a comment....

    That's where I get stuck... how do I test that??

    Thanks,

    Erik Jan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Does cell have comment? (EXCEL 97/2000)

    You can use On Error Resume Next to avoid errors:

    Dim rng As Range
    Dim cel As Range
    On Error Resume Next
    Set rng = Range("A1:B4")
    For Each cel In rng
    cel.Comment.Shape.Fill.ForeColor.RGB = vbCyan
    Next cel
    On Error Goto 0

    or you can use the Comments collection of an entire worksheet:

    Dim cmt As Comment
    For Each cmt In ActiveSheet.Comments
    cmt.Shape.Fill.ForeColor.RGB = vbCyan
    Next

    Note that the code doesn't select any cells.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Does cell have comment? (EXCEL 97/2000)

    It is always faster and usually much better to not select cells if you don't have to.

    You could do something like this:

    <pre>Public Sub Test()
    Dim oCmt As Comment, oCell As Range
    For Each oCmt In Worksheets("Sheet1").Comments
    Set oCell = oCmt.Parent
    If Not Intersect(oCell, Worksheets("Sheet1").Range("MyRange")) Is Nothing Then
    oCmt.Shape.Fill.ForeColor.RGB = vbBlue
    End If
    Next oCmt
    End Sub
    </pre>

    Legare Coleman

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Does cell have comment? (EXCEL 97/2000)

    Yes, your tips helped me out here. Had some remaining problems changing the comment-font but found out myself the textframe-property helps out here.

    I did see that 'learning by recording a macro' is useless in this case...

    Problems solved! Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •