title: setting color to a comment's characters in vba
body: <p>I need to copy a cell in Excel from one range to another range's comment, while keeping its format (size, bold, color, italic...).</p>
  
  <p>My piece of code works, except for color, which throws a Run-Time error '1004': 
  Font size must be between 1 and 409 points.</p>
  
  <p>Which is strange, because size works, and if I comment out color lines (') it works.</p>
  
  <p>Here is my code:</p>
  
  <pre><code>Option Explicit
  
  Function Comment_Format(ByVal Rg_Value As Range, ByVal Rg_Com As Range) As Comment
  Dim i As Long, a As Long
  If Rg_Com.Comment Is Nothing Then Rg_Com.AddComment
  With Rg_Com.Comment
      .Text Text:=Rg_Value.Value2
      .Shape.TextFrame.AutoSize = True
  End With
  
  For i = 1 To Len(Rg_Value.Value2)
      With Rg_Com.Comment.Shape.TextFrame.Characters(i, 1).Font
          .Size = Rg_Value.Characters(i, 1).Font.Size
          'a = Rg_Value.Characters(i, 1).Font.Color
          'If a &gt; 0 Then .Color = a    '  &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt; this line shows the error !!
          .FontStyle = Rg_Value.Characters(i, 1).Font.FontStyle
      End With
  Next i
  Set Comment_Format = Rg_Com.Comment
  End Function
  
  
  Sub test()
  Dim com As Comment
  
  Set com = Comment_Format(Range("a1"), Range("b1"))
  End Sub
  </code></pre>
  
  <p>Thanks for your help.</p>