Change the color of text in a listbox based upon a certain criteria

As title, how can I change the color of text in a listbox based upon a certain criteria? For example, there is four columns in the listbox and the second column contains the numbers. If the number of certain row > 1, I want the text of that row change to red color. How can I do that?

Thanks for your attention.


xacis

Hi Xacis,
Welcome to VBAX.
You can change text colour using the listbox ForeColor property, but you can't have more than one colour.
Regards
MD

So, is this a limitation in VBA? If yes, any other method could solve this problem?
Thx a lot!!!

You could filter your data to show only "highlighted" rows. I can't think of any way to show what you're looking for and give you listbox funcionality
Regards
MD

Thx a lot.

It's not possible to change the colour of an individual item in a listbox.

If you change the BackColor it changes for all items.

Malcolm and Norie sure spell "color" funny.

Quote:

---

Originally Posted by Cyberdude

Malcolm and Norie sure spell "color" funny.

---

Ah, but correctly.:bug:

Quote:

---

Originally Posted by xacis

As title, how can I change the color of text in a listbox based upon a certain criteria? For example, there is four columns in the listbox and the second column contains the numbers. If the number of certain row > 1, I want the text of that row change to red color. How can I do that?

Thanks for your attention.


xacis

---

Hi Xacis,

I have found a way to accomplish this task. Instead of using a listbox I've used listview. Add this control by right clicking on the workpalette and choose 'Microsoft Listview Control 6.0 (SP4)'

Set following code in the userform1.initialize

Code:

---

Option Explicit 'this one above the userform1.initialize

Private Sub UserForm_Initialize()
    Dim startrow As Integer 'beginning of data
    Dim endrow As Integer 'end of data
    Dim pos As Integer 'actual row
    Dim lv_item As Integer 'no of the listview item
    Dim counting As Integer 'loop for processing all items
    startrow = 2
    ' endrow = xlLastRow("Sheet1")
    ' xllastrow is a function found at this forum otherwise use a number for testing
    ' Sheet1 is the name of your sheet
    pos = 2
    lv_item = 1
    With ListView1
        ' gives me headers at the top
        .View = lvwReport
        ' defining the columnheaders
        With .ColumnHeaders
            .Clear
            .Add , , "Column 1", 60
            .Add , , "Column 2", 60
            .Add , , "Column 3", 60
            .Add , , "Column 4", 60
        End With
        .HideColumnHeaders = False
        .Appearance = cc3D
        .FullRowSelect = True
        For counting = startrow To endrow
            If Worksheets("Sheet1").Range("B" & pos).Value > 1 Then
                .ListItems.Add , , Worksheets("Sheet1").Range("A" & pos)
                .ListItems(lv_item).ForeColor = RGB(255, 0, 0)
                .ListItems(lv_item).ListSubItems.Add , , Worksheets("Sheet1").Range("B" & pos)
                .ListItems(lv_item).ListSubItems.Add , , Worksheets("Sheet1").Range("C" & pos)
                .ListItems(lv_item).ListSubItems.Add , , Worksheets("Sheet1").Range("D" & pos)
            Else
                .ListItems.Add , , Worksheets("Sheet1").Range("A" & pos)
                .ListItems(lv_item).ForeColor = RGB(0, 0, 0)
                .ListItems(lv_item).ListSubItems.Add , , Worksheets("Sheet1").Range("B" & pos)
                .ListItems(lv_item).ListSubItems.Add , , Worksheets("Sheet1").Range("C" & pos)
                .ListItems(lv_item).ListSubItems.Add , , Worksheets("Sheet1").Range("D" & pos)
                lv_item = lv_item + 1
                pos = pos + 1
            End If
        Next counting
    End With
End Sub

---

Charlize

Code:

---

.ListItems(lv_item).ListSubItems.Add , , Worksheets("Sheet1").Range("D" & pos))

---

The last ) must be deleted. So no )) but ). Sorry about that.
In if and else clause.

Charlize

Also End If forgotten. Sorry, I wasn't typing in the editor.

Charlize,

I used your code exactly as provided, but have a problem.

The ListView looks great, Headers exactly as expected, but I can't see any data at all. Is it an issue with ranges, or is it a Service Pack issue? Is your code for SP4 exclusively? I don't know what SP is installed on my machine, but it is ListView Control 6.0.

Regards,
Steve

Probably because endrow isn't declared. Under

Code:

---

startrow = 2

---

you add this line

Code:

---

endrow = Worksheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row

---

and the values in column B must be greater than 1