Hi all,

I am new to VBA/Excel, and am just starting to work on some macro's. I have made a macro to create a listbox, filled with information from another worksheet/workbook. Not that hard. The problem is this:

I need to get every entry in column D, until I see a cell with Backgroundcolor 3 (red). Before that red cell and in between the cells filled with words, lots of cells are empty (on a range of D4:D390 for example, only 15 cells contain info). The range should be dynamic.

I tried a do-while until Cells(i,4).Interior.ColorIndex =3, but that did not work, so this was my 2nd guess:

Private Sub UserForm_Initialize()

Dim ListItems As Variant, i As Long
Dim Source As Workbook, r As Integer

With Me.listChangeMachine
.Clear
Application.ScreenUpdating = False

Set Source = Workbooks.Open("C:\Documents\test.xls", False, True)

ListItems = Source.Worksheets("MP").Range("D5:D390").Value

i = 1

For i = 1 To UBound(ListItems)
If ListItems(i) = "" Then
'Delete ListItems(i)
i = i + 1
Else
i = i + 1
End If
Next

Source.Close False
Set Source = Nothing

Application.ScreenUpdating = True

ufChangeMachine.listChangeMachine.List = Application.WorksheetFunction.Transpose(ListItems)

End With

End Sub

I do not know what I'm doing wrong. Every time I change something, another error will pop up. Can anyone help me with that? Thanks so much!

J.