VBA在变量中存储颜色索引

My program is supposed to check the colour index of a cell and then based on its colour, it increases a counter. For some reason I don't seem to be able to store the colour index to a variable though.

我的程序应该检查一个细胞的颜色指数，然后根据它的颜色，增加一个计数器。由于某些原因，我似乎无法将颜色索引存储到一个变量中。

Here is the code:

这是代码:

Dim orange As Integer, green As Integer, blue As Integer, i As Integer, check As Long

For i = 0 To 79

    check = ThisWorkbook.Sheets("Name").Cells(i, 11).Interior.ColorIndex

    If check = 33 Then
        blue = blue + 1
    ElseIf check = 43 Then
        green = green + 1
    ElseIf check = 44 Then
        orange = orange + 1
    End If

Next I

Thanks in advance!

提前谢谢!

3 个解决方案

#1

This is because your i value starts at 0. Cell(0,11) is not a valid cell. Adjust your for-loop to start at 1.

这是因为i值从0开始。单元格(0,11)不是有效单元格。调整for循环从1开始。

Dim orange As Integer, green As Integer, blue As Integer, i As Integer, check As Long

For i = 1 To 79

    check = ThisWorkbook.Sheets("Name").Cells(i, 11).Interior.ColorIndex

    If check = 33 Then
        blue = blue + 1
    ElseIf check = 43 Then
        green = green + 1
    ElseIf check = 44 Then
        orange = orange + 1
    End If

Next I

#2

If you were to include all of the ColorIndexes provided by @Jeeped then you might want to change your coding a bit like so:

如果您要包含@Jeeped提供的所有颜色索引，那么您可能需要像这样修改代码:

Dim orange As Long, green As Long, blue As Long, i As Long, check As Long

For i = 1 To 79

    check = ThisWorkbook.Sheets("Name").Cells(i, 11).Interior.ColorIndex

    Select Case check
    Case 33, 5, 8, 11, 23, 32, 25, 41, 55
        blue = blue + 1
    Case 43, 4, 10, 43, 50
        green = green + 1
    Case 44 To 46
        orange = orange + 1
    Case Else
        'colorNotFoundCounter = colorNotFoundCounter + 1
    End Select

Next i

#3

You can store all type of colors in an array of colors ColorArr.

您可以将所有类型的颜色存储在ColorArr的数组中。

See code below:

请参见下面的代码:

Option Explicit

Sub StoreIntColors_InArray()

Dim ColorArr() As Variant
Dim i As Long, check As Long

ReDim ColorArr(1 To 1000) ' resize color array to large number, per each type of color

For i = 1 To 79
    check = ThisWorkbook.Sheets("Name").Cells(i, 11).Interior.ColorIndex
    If check <> -4142 Then
        ColorArr(check) = ColorArr(check) + 1 ' <-- add 1 to the count of the specific color's array
    End If
Next i

End Sub

#1