PDA

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



touyets
08-28-2010, 04:47 AM
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:


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

Bob Phillips
08-28-2010, 05:23 AM
You can' have different colours in a listbox items AFAIAA

touyets
08-28-2010, 04:39 PM
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"

Bob Phillips
08-29-2010, 02:05 AM
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

Paul_Hossler
08-29-2010, 01:21 PM
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

touyets
08-30-2010, 12:12 AM
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


That did the trick thanks