I am trying to load a listbox by using the cell format as my filter. The
following routine creates a collection of cells that are gray with yellow
text. The problem is, I can only load the last cell into the listbox. Any
help with that final step would be greatly appreciated. An added bonus would
be to limit it to a range.

Thanks,
Steve



Sub ProductGroupFormat()
Dim AllCells As Range, FirstCell As Range, FoundCell As Range
Dim ProductGroup As New Collection

UserForm2.ListBox2.Clear

With Application.FindFormat
.Clear
.Interior.ColorIndex = 15
.Font.ColorIndex = 36
End With

Set FirstCell = ActiveSheet.UsedRange.Find(what:="",
searchformat:=True)

Set AllCells = FirstCell
Set FoundCell = FirstCell

Do
Set FoundCell = ActiveSheet.UsedRange.Find(After:=FoundCell,
what:="", searchformat:=True)
If FoundCell Is Nothing Then Exit Do
Set AllCells = Union(FoundCell, AllCells)
If FoundCell.Address = FirstCell.Address Then Exit Do
Loop

ProductGroup.Add AllCells.Value

For Each Item In ProductGroup
UserForm2.ListBox2.AddItem Item
Next Item

UserForm2.Show

End Sub