同一个数据标签 VBA 中的两种不同颜色

Two different colors in a same datalabel VBA

提问by user2460449

I have a chart on excel with a curve. The curve represents the price of a stock depending of the time (so basically I've a column A full of dates and Column B full of prices and a chart).

我有一个带有曲线的excel图表。曲线代表股票的价格取决于时间（所以基本上我有一个充满日期的 A 列和一个充满价格和图表的 B 列）。

Now for each point in the chart there is a datalabel text as followed

现在对于图表中的每个点都有一个数据标签文本如下

Charts("CHART").SeriesCollection(1).Points(Numpoint).DataLabel.Text = numcomptagesetup & vbCrLf & numcomptagecountdown

"numpoint" is a variable that goes from point 1 to the last one. "numcomptagesetup" and "numcomptagecountdown" are values that goes from 1 to 6 for "numcomptagesetup" and from 1 to 15 for "numcomptagecountdown".

“numpoint”是一个从第 1 点到最后一个的变量。“numcomptagesetup”和“numcomptagecountdown”是“numcomptagesetup”的从1到6的值，“numcomptagecountdown”的值从1到15。

The code above is writing "numcomptagesetup" then jumping two lines then writing "numcomptagecountdown"

上面的代码是写“numcomptagesetup”然后跳两行然后写“numcomptagecountdown”

What I need is, depending on 2 signals, to change the color of "numcomptagesetup" in green or red and the color of "numcomptagecountdown" in green and red too. So in some cases, I'll need to have two different colors in the same datalabeltext.

我需要的是，根据 2 个信号，将“numcomptagesetup”的颜色更改为绿色或红色，并将“numcomptagecountdown”的颜色更改为绿色和红色。所以在某些情况下，我需要在同一个 datalabeltext 中有两种不同的颜色。

I've written this loop and it works at least at the beginning (step by step method) then everything change and the colors aren't at the good place (red instead of green or the contrary) ... Really strange By default, all the points are green, then i change the color of the point to red when I want :

我写了这个循环，它至少在开始时有效（逐步方法）然后一切都改变了，颜色不在好地方（红色而不是绿色或相反）......真的很奇怪默认情况下，所有的点都是绿色的，然后我在需要时将点的颜色更改为红色：

 For numpoint=1 to 100
With Charts("CHART").SeriesCollection(1).Points(Numpoint)
            .HasDataLabel = True
            .DataLabel.Text = numcomptagesetup & vbCrLf & numcomptagecountdown
            .DataLabel.Font.Size = 6
            .DataLabel.Font.Color = vbRed
            .MarkerSize = 5
End With
If Signal = 2 Then Charts("CHART").SeriesCollection(1).DataLabels.Select 
  With Charts("CHART").SeriesCollection(1).Points(Numpoint).DataLabel.Format.TextFrame2.TextRange.Characters(1, 3).Font.Fill 
    .Visible = msoTrue 
    .ForeColor
    .RGB = RGB(0, 176, 80) .Solid 
  End With 
End If

If Signal2 = 2 Then
    Charts("CHART").SeriesCollection(1).DataLabels.Select
    With Charts("CHART").SeriesCollection(1).Points(Numpoint).DataLabel.Format.TextFrame2.TextRange.Characters(4, 1).Font.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 176, 80)
        .Solid
    End With
End If

I hope you understand my problem ! If not you can ask, i'll answer as quickly as I can

我希望你明白我的问题！如果没有你可以问，我会尽快回答

Thank you!!

谢谢！！

采纳答案by David Zemens

I am stumped by this. Even the macro recorder gives me code similar to yours, but when I run it, the color is applied to alldatalabels in the Series.DataLabels.

我被这难住了。甚至宏记录器也给了我类似于你的代码，但是当我运行它时，颜色会应用于Series.DataLabels 中的所有数据标签。

I have a possible workaround, which would be to add TextBox shapes to the chart, and to position them directly on top of the data labels. Then, remove the datalabels at the end.

我有一个可能的解决方法，即向图表添加 TextBox 形状，并将它们直接放置在数据标签的顶部。然后，删除最后的数据标签。

'## This is from your original code:
For numpoint=1 to 100
    With Charts("CHART").SeriesCollection(1).Points(Numpoint)
        .HasDataLabel = True
        .DataLabel.Text = numcomptagesetup & vbCrLf & numcomptagecountdown
        .DataLabel.Font.Size = 6
        .DataLabel.Font.Color = vbRed
        .MarkerSize = 5
    End With
Next

'## Now, I make some changes:
Dim srs As Series
Dim dl As DataLabel
Dim dlText As String

Set srs = Charts("CHART").SeriesCollection(1) '## I find it easier to work with objects, so I use variable "srs"

'## Delete any textboxes that might be leftover from previous.
For Each shp In cht.Shapes
    shp.Delete
Next

For Each dl In srs.DataLabels  '## object-oriented programming, use a DataLabel object variable :
    dlText = dl.Caption
    '## Add a textbox to overlay the datalabel
    Set tb = cht.Shapes.AddTextbox(msoTextOrientationHorizontal, dl.Left, dl.Top, dl.Width, dl.Height)
    With tb.TextFrame2
        .VerticalAnchor = msoAnchorMiddle
        .WordWrap = msoFalse
        .AutoSize = msoAutoSizeShapeToFitText
        With .TextRange
            .Text = dlText
            .Font.Size = dl.Format.TextFrame2.TextRange.Font.Size
            '## Apply character color format to the textbox:
            If Signal = 2 Then
                With .Characters(, 3).Font.Fill
                    .Visible = msoTrue
                    .ForeColor.RGB = RGB(0, 176, 80)
                    .Solid
                End With
            End If
            '## Apply character color format to the textbox:
            If Signal2 = 2 Then
                With .Characters(4, 1).Font.Fill
                    .Visible = msoTrue
                    .ForeColor.RGB = RGB(0, 176, 80)
                    .Solid
                End With
            End If
        End With
    End With
Next

'## Finally, turn off the datalabels that you don't need anymore.
srs.HasDataLabels = False

回答by Patrick Lepelletier

i've come up with something else :

我想出了别的东西：

 With ChartObj.Chart.SeriesCollection(1).DataLabels.Format.TextFrame2.TextRange.Characters.Font.fill                                    
         .TwoColorGradient msoGradientDiagonalUp, 1
         .GradientAngle = 45
         .GradientStops(1).Position = 0 '.2
         .GradientStops(2).Position = 1 '0.8
         .ForeColor.RGB = RGB(0, 0, 0)
         .BackColor.RGB = RGB(255, 255, 255)
 End With

wich gives me a gradient color for the datalabels.

至极为我提供了数据标签的渐变颜色。