Anyone see the prob in this code?

[Joseph]

Private Sub CommandButton1_Click()
sheets("sheet3").Range("A1").Select
For I = 1 To 191
While Len(ActiveCell.Value) > 0
If ActiveCell.Interior.ColorIndex = 37 Then
UserForm1.ComboBox1.AddItem (ActiveCell.Value)
ElseIf ActiveCell.Interior.ColorIndex = xlNone Then
ActiveCell.Offset(1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
Wend
Next I
CommandButton2.Enabled = True

End Sub

The problem Im getting is that it isn't getting all the values into th
combobox? It usually only gets 3.

Thank

 

[Bob Phillips]

I just tried it and set 10 cells to that colour and got them all!

 

[kkknie]

Not sure of your exact error, but I'd change the code to this:

Private Sub CommandButton1_Click()

Sheets("Sheet3").Range("A1").Select
Dim r As Range

For Each r In Range("A1:A191")
If r.Interior.ColorIndex = 37 Then UserForm1.ComboBox1.AddIte
(r.Value)
Next
CommandButton2.Enabled = True

End Sub