Colouring of rows in Listview based on the criteria in one of the column in list view

Question

Hello,

        I have a following excel data from which a list view is populated:

Row Labels	Sum of Balance	Sum of Wastage	Sum of Low Alert
Blue Ver.3	3030	0	175
Blue Ver.4	150	0	175
Blue Ver.6	1000	0	175
Green L202	175	0	175
Magenta Ver.3	100	0	175
Mixing Solution	100	0	175
Orange Ver.3	75	0	175
Orange Ver.6	65	0	175
Red Ver.3	357.66	0.61	175
Red Ver.4	44.12	0.18	175
Yellow Ver.3	100	0	175
Yellow Ver.4	55	0	175

I wish to show the low alert rows colour differently in listview. That means whenever the sum of balance is equal to or lower than vaule in sum of low alert the figure in sum of balance gets into different colour or the row gets different colour, can anybody help to sort out the same

Answer 1

Hi,

Try this

1. Click on cell B2 and go to Home > Conditional formatting > New Rule > Use a formula to determine which cells to Format
2. In the Formula bar there, enter this

=B2<=D2

3. Click on Format > Fill > Red
4. Click on OK/Apply
5. Copy cell B2, select B3:B13, right Click > Paste Special > Formats > OK

Hope this helps.

Answer 2

Hello,

        Thanks for the response, the solution suggested by you orks perfects for the excel sheet but does not reflect in ListView. I wish to have a listview text or row to be coloured based on values in another colomun.

Answer 3

Hi,

What do you mean by List View.  Please take an example/share screenshots

Answer 4

Following code generates ListView in userform, I wish to have the colour rows in Listview1 based on the crieteria

Private Sub UserForm_Activate()

 'Set some of the properties for the ListView

       'Set some of the properties for the ListView

     UserForm4.TextBox1.Value = Format(Now(), "dd/mm/yyyy")

     UserForm4.Label2 = UserForm4.TextBox1.Value

     TextBox2.Text = Sheet7.Cells(1, 3)

     UserForm4.Label3 = UserForm4.TextBox2.Value

    With Me.ListView1

        .Gridlines = True

        .HideColumnHeaders = False

        .View = lvwReport

        .ColumnHeaders.Add , , "Polymer Version", 155

        .ColumnHeaders.Add , , "Avilable Quantity (Kg)", 175

        .ColumnHeaders.Add , , "Wastage Quantity (Kg)", 175

    End With

    With Me.ListView2

        .Gridlines = True

        .HideColumnHeaders = False

        .View = lvwReport

        .ColumnHeaders.Add , , "", 0

        .ColumnHeaders.Add , , "", 0

        .ColumnHeaders.Add , , "Job Number", 80

        .ColumnHeaders.Add , , "Hybrid/ Variety", 100

        .ColumnHeaders.Add , , "Batch Number", 80

        .ColumnHeaders.Add , , "Quantity of Seeds Coated (Kg)", 160

        .ColumnHeaders.Add , , "Polymer Used", 100

         End With

    'Call the sub to fill the ListView1

    Call LoadListView1

    'Call the sub to fill the ListView2

    Call LoadListView2

End Sub

Private Sub LoadListView1()

    'Declare the variables

    Dim wksSource As Worksheet

    Dim rngData As Range

    Dim rngCell As Range

    Dim LstItem As ListItem

    Dim RowCount As Long

    Dim ColCount As Long

    Dim i As Long

    Dim j As Long

    'Set the source worksheet

    Set wksSource = Worksheets("Stock Pivot")

    'Set the source range

    Set rngData = wksSource.Range("A6").CurrentRegion

    'Count the number of rows in the source range

    RowCount = rngData.Rows.Count

    'Count the number of columns in the source range

    ColCount = rngData.Columns.Count

    'Fill the ListView1

    For i = 3 To RowCount

        Set LstItem = Me.ListView1.ListItems.Add(Text:=rngData(i, 1).Value)

        For j = 2 To ColCount

            Select Case j

                Case 2 To 5

                    LstItem.ListSubItems.Add Text:=Format(rngData(i, j).Value, "0.00")

                Case Else

                    LstItem.ListSubItems.Add Text:=rngData(i, j).Value

            End Select

        Next j

    Next i

End Sub

Private Sub LoadListView2()

    'Declare the variables

    Dim wksSource As Worksheet

    Dim rngData As Range

    Dim rngCell As Range

    Dim LstItem As ListItem

    Dim RowCount As Long

    Dim ColCount As Long

    Dim i As Long

    Dim j As Long

    'Set the source worksheet

    Set wksSource = Worksheets("Coating Status")

    'Set the source range

    Set rngData = wksSource.Range("A2").CurrentRegion

    'Count the number of rows in the source range

    RowCount = rngData.Rows.Count

    'Count the number of columns in the source range

    ColCount = rngData.Columns.Count

    'Fill the ListView2

    For i = 3 To RowCount

        Set LstItem = Me.ListView2.ListItems.Add(Text:=rngData(i, 1).Value)

        For j = 2 To ColCount

            Select Case j

                Case 6 To 7

                    LstItem.ListSubItems.Add Text:=Format(rngData(i, j).Value, "0.00")

                Case Else

                    LstItem.ListSubItems.Add Text:=rngData(i, j).Value

            End Select

        Next j

    Next i

End Sub

Answer 5

Hi,

   I tried something with the code (marked as bold) but it does not seem to be working can anybody help here to achieve the result, the code is as follows:

Private Sub UserForm_Activate()

 'Set some of the properties for the ListView

       'Set some of the properties for the ListView

     UserForm4.TextBox1.Value = Format(Now(), "dd/mm/yyyy")

     UserForm4.Label2 = UserForm4.TextBox1.Value

     TextBox2.Text = Sheet7.Cells(1, 3)

     UserForm4.Label3 = UserForm4.TextBox2.Value

    With Me.ListView1

        .Gridlines = True

        .HideColumnHeaders = False

        .View = lvwReport

        .ColumnHeaders.Add , , "Polymer Version", 155

        .ColumnHeaders.Add , , "Avilable Quantity (Kg)", 175

        .ColumnHeaders.Add , , "Wastage Quantity (Kg)", 175

        .ColumnHeaders.Add , , "Cutoff (Kg)", 175

    End With

    With Me.ListView2

        .Gridlines = True

        .HideColumnHeaders = False

        .View = lvwReport

        .ColumnHeaders.Add , , "", 0

        .ColumnHeaders.Add , , "", 0

        .ColumnHeaders.Add , , "Job Number", 80

        .ColumnHeaders.Add , , "Hybrid/ Variety", 100

        .ColumnHeaders.Add , , "Batch Number", 80

        .ColumnHeaders.Add , , "Quantity of Seeds Coated (Kg)", 160

        .ColumnHeaders.Add , , "Polymer Used", 100

         End With

    'Call the sub to fill the ListView1

    Call LoadListView1

    'Call the sub to fill the ListView2

    Call LoadListView2

End Sub

Private Sub LoadListView1()

    'Declare the variables

    Dim wksSource As Worksheet

    Dim rngData As Range

    Dim rngCell As Range

    Dim LstItem As ListItem

    Dim RowCount As Long

    Dim ColCount As Long

    Dim i As Long

    Dim j As Long

    Dim k As Long

    'Set the source worksheet

    Set wksSource = Worksheets("Stock Pivot")

    'Set the source range

    Set rngData = wksSource.Range("A6").CurrentRegion

    'Count the number of rows in the source range

    RowCount = rngData.Rows.Count

    'Count the number of columns in the source range

    ColCount = rngData.Columns.Count

    'Fill the ListView1

    For i = 3 To RowCount

        Set LstItem = Me.ListView1.ListItems.Add(Text:=rngData(i, 1).Value)

        For j = 2 To ColCount

            Select Case j

                Case 2 To 6

                    LstItem.ListSubItems.Add Text:=Format(rngData(i, j).Value, "0.00")

                   Case Else

                    LstItem.ListSubItems.Add Text:=rngData(i, j).Value

               End Select

           Next j

    Next i

          For k = 1 To ColCount

            Select Case k

Case 2 To 6

If LstItem.ListSubItems.Add.Text = rngData(1, 2).Value > LstItem.ListSubItems.Add.Text = rngData(1, 4).Value Then

LstItem.ListSubItems.Add.ForeColor = vbRed

Else

LstItem.ListSubItems.Add.ForeColor = vbGreen

End If

End Select

Next k

End Sub

Private Sub LoadListView2()

    'Declare the variables

    Dim wksSource As Worksheet

    Dim rngData As Range

    Dim rngCell As Range

    Dim LstItem As ListItem

    Dim RowCount As Long

    Dim ColCount As Long

    Dim i As Long

    Dim j As Long

    'Set the source worksheet

    Set wksSource = Worksheets("Coating Status")

    'Set the source range

    Set rngData = wksSource.Range("A2").CurrentRegion

    'Count the number of rows in the source range

    RowCount = rngData.Rows.Count

    'Count the number of columns in the source range

    ColCount = rngData.Columns.Count

    'Fill the ListView2

    For i = 3 To RowCount

        Set LstItem = Me.ListView2.ListItems.Add(Text:=rngData(i, 1).Value)

        For j = 2 To ColCount

            Select Case j

                Case 6 To 7

                    LstItem.ListSubItems.Add Text:=Format(rngData(i, j).Value, "0.00")

                Case Else

                    LstItem.ListSubItems.Add Text:=rngData(i, j).Value

            End Select

        Next j

    Next i

End Sub