在VBA中为评论的字符设置颜色 [英] setting color to a comment's characters in vba

问题描述

我需要在Excel中将一个单元格从一个范围复制到另一个范围的注释，同时保持其格式(大小，粗体，颜色，斜体...).

I need to copy a cell in Excel from one range to another range's comment, while keeping its format (size, bold, color, italic...).

我的代码可以工作，除了颜色，它会抛出运行时错误'1004':字体大小必须在1到409点之间.

My piece of code works, except for color, which throws a Run-Time error '1004': Font size must be between 1 and 409 points.

这很奇怪，因为尺寸会起作用，而且如果我注释掉色线(')也会起作用.

Which is strange, because size works, and if I comment out color lines (') it works.

这是我的代码:

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 > 0 Then .Color = a    '  <<<<<<<<<<<<<<< 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

感谢您的帮助.

推荐答案

我最好使用 ColorIndex 而不是 Color 先着色:

I had better luck using ColorIndex rather than Color and coloring first:

Sub MAIN2()
    Call Comment_Format(Range("a1"), Range("b1"))
End Sub

Sub Comment_Format(Rg_Value As Range, Rg_Com As Range)
    Dim i As Long
    With Rg_Com
        .ClearComments
        .AddComment
        .Comment.Text Text:=Rg_Value.Text
        L = Len(Rg_Value.Text)

        For i = 1 To L
            .Comment.Shape.TextFrame.Characters(i, 1).Font.ColorIndex = Range("A1").Characters(i, 1).Font.ColorIndex
        Next i
    End With

    For i = 1 To L
        With Rg_Com.Comment.Shape.TextFrame.Characters(i, 1).Font
            .Size = Rg_Value.Characters(i, 1).Font.Size
            .Bold = Rg_Value.Characters(i, 1).Font.Bold
            .Italic = Rg_Value.Characters(i, 1).Font.Italic
        End With
    Next i

End Sub

我给了谁

EDIT#1:

在 Excel 2007/Win 7 中，使用注释

这篇关于在VBA中为评论的字符设置颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！