Solved: Changing color of item in listbox based on value of specific cell

Hi again,

you've been amazing at helping with my first issue. Here's a new one i've tried to work out.

The listbox1 I have at the minute creates an item for each visible cell in column 1. I would like for that specific item to show up in red if the value of the cell from the same row but in column 5 is different than nothing (that's to say : <>""). What i have so far:

[vba]
ListBox1.Clear
Dim c As Long
c = 1
For Each cll In Range("Table1").Columns(1).SpecialCells(xlCellTypeVisible).Cells
ListBox1.AddItem cll.Value
If Cells(cll, 5).Value <> "" Then
ListBox1.items(c).ForeColor = vbRed
End If
c = c + 1
Next cll
[/vba]

You can' have different colours in a listbox items AFAIAA

That kinda sucks for me :|

okay, then is it possible to have what is it that column 5 cell added to the name of the item in the listbox when there is something in that respective cell?

so the item would look like this :
" whatever is in cell A11 for example" & "followed by whatever is in cell E11 if there is something in it"

[vba]
ListBox1.Clear
For Each cll In Range("Table1").Columns(1).SpecialCells(xlCellTypeVisible).Cells
ListBox1.AddItem cll.Value & IIf(cll.Offset(0, 4) = "", "", " (" & cll.Offset(0, 4) & ")")
Next cll
[/vba]

Charlize came up with a work around using a ListView control in 9/8/2006

http://www.vbaexpress.com/forum/showthread.php?t=9152

Might be worth a look

Paul

Quote:

---

Originally Posted by xld

[vba]
ListBox1.Clear
For Each cll In Range("Table1").Columns(1).SpecialCells(xlCellTypeVisible).Cells
ListBox1.AddItem cll.Value & IIf(cll.Offset(0, 4) = "", "", " (" & cll.Offset(0, 4) & ")")
Next cll
[/vba]

---

That did the trick thanks