VBA Store color index in variable

Question

My program should check the color index of the cell, and then depending on its color, it increments the counter. For some reason, it seems like I can't store the color index for the variable.

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!

Answer 1

This is because your I value starts at 0. Cell (0.11) is not a valid cell. Set the for-loop to 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

Answer 2

If you include all ColorIndexes provided by @Jeeped, you can change your encoding a bit as follows:

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

Answer 3

ColorArr.

:

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 array
    End If
Next i

End Sub