Excel-VBA渐变色标

问题描述

通过宏代码对单元格应用水平渐变填充效果的最佳方法是什么?

What is the best way to apply horizontal gradient fill effect to a cell through macro code?

我已经在Excel中设置了所需的渐变(右键单击单元格B1，设置单元格格式..."，填充"，填充效果"，两种颜色"，水平"，全部").

I've set the desired gradient in Excel (right-click on cell B1, Format Cells..., Fill, Fill Effects, Two Colors, Horizontal, "Okay" all).

然后我有以下代码来找出如何通过代码来表示这一点.当我单步执行代码时，可以使用locals窗口检查myrange对象的渐变和色标:

I then have the following code to find out how to represent this via code. When I step through the code, I can use the locals window to inspect the gradient and colorstops of the myrange object:

Dim myrange As range
Set myrange = ActiveSheet.range("B1")

使用此信息，我现在可以在宏中对信息进行硬编码，以期通过代码复制渐变填充:

Using this information, I can now hard-code the information in a macro, in hopes of duplicating the gradient fill by code:

'First, delete any previous gradient colorstops
For Each cs In myrange.Interior.Gradient.ColorStops
  cs.Delete
Next

'Then, assign the desired colorstops in the gradient
With myrange.Interior.Gradient.ColorStops
  .add color = 16777215
  Position = 0
  ThemeColor = 1
  TintAndShade = 0

  .add color = 7961087
  Position = 0.5
  ThemeColor = 0
  TintAndShade = 0

  .add color = 16777215
  Position = 1
  ThemeColor = 1
  TintAndShade = 0

End With

不幸的是，这导致看起来完全错误的事情.最明显的错误是即使我调整了RGB值，渐变也是黑白的.

Unfortunately, this results in something that looks totally wrong. The most obvious thing that's wrong is that the gradient is in black and white, even as I adjust the RGB values.

这里还应该添加其他内容吗?

Is there something else that should be added here?

推荐答案

ColorStops的分配是无效代码.您需要添加色标，然后设置其属性.宏记录器可以正确执行此操作.

The assignment of ColorStops is not valid code. You need to add the colorstops and then set their properties. The macro recorder does it correctly.

Sub SetGradient()
Dim myrange As Range
Set myrange = ThisWorkbook.Sheets("Sheet1").Range("B1")

    With myrange.Interior
        .Pattern = xlPatternLinearGradient
        .Gradient.Degree = 90
        .Gradient.ColorStops.Clear
    End With
    With myrange.Interior.Gradient.ColorStops.Add(0)
        .Color = 16777215
        .TintAndShade = 0
    End With
    With myrange.Interior.Gradient.ColorStops.Add(0.5)
        .Color = 7961087
        .TintAndShade = 0
    End With
    With myrange.Interior.Gradient.ColorStops.Add(1)
        .Color = 16777215
        .TintAndShade = 0
    End With
End Sub

这篇关于Excel-VBA渐变色标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！