Finding a value in my table

[mongoose]

I am trying to find a cell in this table based on certain criteria. This is the criteria:
1) Search "Start Date" column top to bottom for the cells with a colorindex of 35; searching only the rows which are not hidden by filters
2) Find the first cell in column "Start Date" with a colorindex of 35 for which the adjacent cell in column "End Date" has an indexcolor other than 35
3) Get the date in that particular cell found
4) Filter out all days prior to that day; show only that cell and the ones below it
5) Keep the column sort in place; do not clear the sorting
6) Then I will assign it to a button

I haven't done much with ListObjects (tbls, pvtTbls, etc.) and I come from a different programming program. Here's my SIMPLE code so far and I keep hitting road blocks so I'm not sure where I'm headed...


Private Sub CustomTest()
'Intiansiate objects and setup variables
Dim c As Range, tbl As ListObject, FoundCell As Range

'Set object/variable values
Set tbl = ActiveWorkbook.Worksheets(1).ListObjects("Table1")

For i = 1 To tbl.Range.Rows.Count
MsgBox Range(i, tbl.Range.Columns(2))

Next i

MsgBox tbl.Range.Rows.Count
End Sub



25850

[Leith Ross]

Hello mongoose,

I am assuming all the dates are in the same year, correct?

What version of Office are you using?

[mongoose]

2013. Yes they are but I would like to plan for if they are not as well.

[Leith Ross]

Hello Mongoose,

If I understand what you want to do then this code should do it.



Private Sub CustomTest()


'Intiansiate objects and setup variables
Dim tbl As ListObject

'Set object/variable values
Set tbl = ActiveWorkbook.Worksheets(1).ListObjects("Table1")

With tbl.AutoFilter
' Start Date
With .Filters(2)
.Operator = xlFilterCellColor
.Criteria1.ColorIndex = 35
End With

.ApplyFilter

' End Date
With .Filters(3)
.Operator = xlFilterCellColor
.Criteria1.ColorIndex = xlColorIndexNone
End With

.ApplyFilter
End With

End Sub

[mongoose]

I'm sorry for the delay in response. I've been out of town. I've tried the code you posted but it hides all rows in the table.

The first image shows the data before I run the macro. The 2nd shows the data after the macro has run.

25921 25922

Here are the conditions I am trying to accomplish..
25923