View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Trevor[_3_] Trevor[_3_] is offline
external usenet poster
 
Posts: 3
Default Filling a listbox depending on font index

I have a worksheet with a range called 'Ledger'

What I want to do is fill a LISTBOX with the values UNLESS the first
cell is font color red (colorindex 3).

I then need to use this listbox to select items and on exiting colour
cell A+n red if I check them.

The problem I have to overcome is that by doing an If/Then to check if
the first cell is Red the range count goes awry :(

Here is my code:

Private Sub UserForm_Initialize()
Dim i As Single
Dim nRows As Integer
Dim Fmt As String 'format string

Fmt = "##0.00"

' Count rows in range 'Ledger'
nRows = Range("Ledger").Rows.Count ' Ledger is a range

With Me.ListBox1
.Clear ' remove existing content
.ColumnCount = 8
.ColumnWidths = "0,130,0,100,70,70,0,0"

For i = 1 To nRows

' Add 4 to range as this is where it starts (5th row down)

' Add item to the list IF it's colorindex is NOT red (colorindex 3)
If Range("A" & i + 4).Font.ColorIndex < 3 Then

.AddItem ' add a new row
.List(i - 1, 0) = Range("A" & i + 4).Value
.List(i - 1, 1) = Range("B" & i + 4).Value
.List(i - 1, 2) = Range("C" & i + 4).Value
.List(i - 1, 3) = Range("D" & i + 4).Text
.List(i - 1, 4) = Range("E" & i + 4).Value
.List(i - 1, 5) = Range("F" & i + 4).Value
.List(i - 1, 6) = Range("G" & i + 4).Value
.List(i - 1, 7) = Range("H" & i + 4).Value

End If

End With

End Sub