vba 在不使用循环的情况下更改单元格范围的内部颜色

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/9325072/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 15:15:37  来源:igfitidea点击:

Change interior color of range of cells without using loops

excelvba

提问by John Smith

How to make this work?

如何使这项工作?

Range(Cells(1,1),Cells(height,width)).Interior.Color=colorArray

colorArray is one-dimensional array of Long Integers of length (width*height) containing color values.

colorArray 是包含颜色值的长度 (width*height) 长整数的一维数组。

The above code returns

上面的代码返回

Type mismatch error.

类型不匹配错误。

For i = 1 to height
   For j = 1 to width
      t=(i-1)*width+j
      Cells(i,j).Interior.Color=colorArray(t)
   Next
Next

This code works but is too slow. I dont want to use loops.

此代码有效,但速度太慢。我不想使用循环。

Range(Cells(1,1),Cells(height,width)).Value=colorArray

This code fills the range with color values from the colorArray with no error. I want a similar code to change background color of cells of this range.

此代码使用 colorArray 中的颜色值填充范围,没有错误。我想要一个类似的代码来更改此范围内单元格的背景颜色。

Please help.

请帮忙。

ReDim colorArray(1 To width*height) As Long

Sample code by Siddharth Rout:

Siddharth Rout 的示例代码:

Sub Sample()
    Dim colorArray(21) 'or Dim colorArray(21) As Long/Integer
    Dim Height As Long, Width As Long

    For i = 0 To 21
        colorArray(i) = i
    Next

    Height = 10
    Width = 2

    Range(Cells(1, 1), Cells(Height, Width)).Interior.Color = colorArray

End Sub

This code also returns

此代码也返回

"Run-time error: '13' Type mismatch"

“运行时错误:‘13’类型不匹配”

回答by Scott

I do believe the problem lies in your call. You should use:

我相信问题出在你的电话上。你应该使用:

Range(Cells(1, 1), Cells(Height, Width)).Interior.Color***Index*** = colorArray

That is my experience using cell background colors in Excel VBA.

这是我在 Excel VBA 中使用单元格背景颜色的经验。

-Scott

-斯科特