how to get listbox to populate all rows in range only if cell in column h is colored red

Question

I'm trying to populate information from range ("A3:H150") to a list box in a user form, only if the cells in column H are colored red, i.e. .Interior.ColorIndex = 3. The code I have still populates the list box with all data regardless if a cell in column H is red or not.

Dim lbtarget As MSForms.ListBox
Dim rngSource As Range
Set rngSource = Sheet1.Range("A3:H40")
Dim RNG As Range
Set RNG = Sheet1.Range("H3:H40")
Dim Cell As Range

Set lbtarget = Me.ListBox1
With lbtarget
    .ColumnCount = 8
    .ColumnWidths = "100;100;100;100;100;100;60;60"
    For Each Cell In RNG
        If Cell.Interior.ColorIndex = 3 Then
            .List = rngSource.Cells.Value
        End If
    Next
End With

`rngSource.Cells.Value` `rngsource` doesn't change, it's static. You will need to use `Add` off your `cell` — Nathan_Sav, Dec 06 '19 at 18:55
sorry i miss read, i tried using "add" and still not working. getting (object does't support this property or method) — Byron, Dec 06 '19 at 19:14
what causes the color of the cells in H to be red? Conditional Formatting? If so, why not use the CF test itself instead of using the color of the cell? Also, IIRC, a cell that is colored via CF will not have the Interior.ColorIndex to test for. CF doesn't change the Interior.ColorIndex I don't think. — SmileyFtW, Dec 06 '19 at 19:32
cells in column h that are RED are colored by a different code `(if cell.value = "this" then cell.interior.colorindex = 3)` — Byron, Dec 06 '19 at 19:36
the .List is populating the listbox with an array; in your case the array is a single element array each time through the loop. Either populate an array and then `.List = myArray' or use the AddItem method each time through the loop — SmileyFtW, Dec 06 '19 at 19:43
As has been pointed out by Nathan, your rngsource doesn't change, use the row of the cell being looped to get the row of the particular row in rngsource to Add — SmileyFtW, Dec 06 '19 at 19:46
To add onto Smiley's point, dimension an array to the size of your total range, then loop through and add, e.g., `if not cells(i+3,"H").Interior.ColorIndex = 3 then arr(i) = cells(i+3,"H").value`; otherwise your if-statement would be what sets the condition for conditional formatting colors. — Cyril, Dec 06 '19 at 19:51

SmileyFtW · Accepted Answer · 2019-12-06T21:15:40.807

   For Each Cell In RNG
        If Cell.Interior.ColorIndex = 3 Then
        .AddItem Sheet1.Range(Sheet1.Cells(Cell.Row,1),Sheet1.Cells(Cell.Row,8))
        End If
        Next
    End With

This should do it:

Private Sub fillListBox()
    Dim myform As UserForm1
    Set myform = New UserForm1

    Dim loopRange As Range
    With Sheet1
        Set loopRange = .Range(.Cells(1, 8), .Cells(10, 8))
    End With
    With myform.ListBox1
        Dim Cell As Range
        Dim indexCounter As Long
        indexCounter = -1
        For Each Cell In loopRange
            If Cell.Interior.ColorIndex= 3 Then
                indexCounter = indexCounter + 1
                .AddItem Sheet1.Cells(Cell.Row, 1).Value
                Dim colCounter As Long
                For colCounter = 2 To 8
                    .List(indexCounter, colCounter - 1) = Sheet1.Cells(Cell.Row, colCounter).Value
                Next
                '
            End If
            Next
    End With
End Sub

i tried that and get (expected function or variable) on the .additem — Byron, Dec 06 '19 at 20:13
Oops, the "=" shouldn't be there. Stand by.. I'm missing something — SmileyFtW, Dec 06 '19 at 20:50

T.M. · Answer 2 · 2019-12-09T18:35:36.717

Assign restructured array to .List property

Procedure doFillListBox

Restructures the entire data set via the Application.Index() function as well as two help functions and
assigns the restructured array to the indicated listbox via its .List property in one single code line, also known as Array method; see section [1]) (instead of adding listbox items one by one, aka as AddItem method).
layouting is done in section [2]:

Sub doFillListBox(lbTarget As MSForms.ListBox, rng As Range)
    With lbTarget
    ' =============================
    ' [1] restructure listbox items
    ' -----------------------------
      .List = Application.Index(rng.Value, getRowNums(rng), getColNums(rng))

    ' =============================
    ' [2] layout listbox
    ' -----------------------------
      .ColumnCount = rng.Columns.Count
      .ColumnWidths = "100;100;100;100;100;100;60;60"
    End With
End Sub

Related link: Some pecularities of the Application.Index function

Helper functions called by above procedure

Function getRowNums(rng As Range, _
         Optional ByVal ColNo As Long = 8, _
         Optional ByVal backgroundColor = 3) As Variant()
'   Purpose: return "vertical" array with row numbers not marked in red background color (e.g.3)
'   Note:    column number default is the 8th column, default background color is red (3)
    Dim n&
    n = rng.Rows.Count
  ' [1] list uncolored row numbers within temporary array
    ReDim tmp(1 To n)                               ' provide for maximum length
    Dim i&, ii&                                     ' row numbers in column H, items counter
    For i = 1 To n                                  ' iterate through column H cells
        If rng.Cells(i, ColNo).Interior.ColorIndex <> backgroundColor Then  ' check criteria
            ii = ii + 1                             ' increment new items counter
            tmp(ii) = i                             ' enter "old" row number
        End If
    Next i
    ReDim Preserve tmp(1 To ii)                     ' reduce to actually needed length

  ' [2] return "vertical" list of needed row numbers
    getRowNums = Application.Transpose(tmp)         ' transpose to 2-dim array

End Function

Function getColNums(rng As Range) As Variant()
  ' Purpose: return all column numbers in a "flat" array, e.g. via Array(1,2,3,4,5,6,7,8)
    getColNums = Application.Transpose(Evaluate("row(1:" & rng.Columns.Count & ")"))
End Function

Example call

Assuming you want to use the click event of a command button control to populate a given ListBox by referencing the data range e.g. via the sheet's CodeName:

Private Sub CommandButton1_Click()
  ' Note: change control names and range reference following your needs
    doFillListBox Me.ListBox1, Sheet1.Range("A3:H150")   ' reference e.g. to CodeName Sheet1
End Sub

Note: The (Name) property is the sheet's CodeName identifier in the VB Editor's Tool window as opposed to the user-modifiable "tab" Name of the worksheet.

@Byron - sent you an alternative approach demonstrating some advanced features of the `Application.Index()` function, possibly helpful :-) — T.M., Dec 17 '19 at 18:38

how to get listbox to populate all rows in range only if cell in column h is colored red

2 Answers2