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

[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