vba colorindex if or statement not working

[workindan]

I'm making a basic, quick macro to add values to a cell if it's green or blue. users inputed various shades of green and blue. I pulled all the values:
'blue 33,34,37,41,8
'green 42,35,4
When I try to do an if-then statement with multiple conditions, it returns true for any color. In the example below, every Blue(41) will put a 1 in cell A, but any cell that's not Blue(41) will have a 1.

Why is this? Seemed like a simple, fast way to do this...




For i = 3 To 4000
If IsNull(Range("C" & i)) = True Then
GoTo emptycell
End If
If Range("C" & i).Interior.ColorIndex = 41 Then
Range("A" & i).Value = 1
ElseIf Range("C" & i).Interior.ColorIndex = 4 Or 35 or 42 Then
Range("A" & i).Value = 2
End If
emptycell:
Next i

 

[VoG]

Try

ElseIf Range("C" & i).Interior.ColorIndex = 4 Or Range("C" & i).Interior.ColorIndex = 35 Or Range("C" & i).Interior.ColorIndex = 42 Then

but Select Case would be better than If.

 

[Norie]

The logic statement in your If will always be true.

Try this.

    Select Case Range("C" & i).Interior.ColorIndex
        Case 41
            Range("A" & i).Value = 1
        Case 4, 35, 42
            Range("A" & i).Value = 2
    End Select

Or this.

    If Range("C" & i).Interior.ColorIndex = 41 Then
        Range("A" & i).Value = 1
    ElseIf Range("C" & i).Interior.ColorIndex = 4 Or Range("C" & i).Interior.ColorIndex = 35 Or Range("C" & i).Interior.ColorIndex = 42 Then
        Range("A" & i).Value = 2
    End If

Both are basically the same but I like one better than the other, don't know why.

 

[workindan]

I went with your case select suggestion....

Select Case Range("C" & i).Interior.ColorIndex
Case 33, 34, 37, 41, 8
Range("A" & i).Value = 1
Case 42, 35, 4
Range("A" & i).Value = 2
End Select

This worked great! Thanks for the help.