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

[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

[mdmackillop]

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

[xacis]

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

[mdmackillop]

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

[xacis]

Thx a lot.

[Norie]

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.

[Cyberdude]

Malcolm and Norie sure spell "color" funny.

[Norie]

Malcolm and Norie sure spell "color" funny.

Ah, but correctly.

[Charlize]

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

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

[Charlize]

.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

[Charlize]

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

[steve.t]

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

[Charlize]

Probably because endrow isn't declared. Under

startrow = 2

you add this line

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

and the values in column B must be greater than 1