Consulting

Results 1 to 6 of 6

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

  1. #1
    VBAX Regular
    Joined
    Aug 2010
    Posts
    7
    Location

    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]

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    You can' have different colours in a listbox items AFAIAA
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular
    Joined
    Aug 2010
    Posts
    7
    Location
    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"

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    [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]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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

  6. #6
    VBAX Regular
    Joined
    Aug 2010
    Posts
    7
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •