VBA AutoFilter results in Run-Time error '1004' No cells were found." error (BUT the records ARE there)

Hi Experts,

Really hoping someone is out there on a Sunday afternoon to help me with this issue and preserve my sanity.

I have two procedures (View_Trip_Schedule and View_Hotel_Schedule) that invoke AutoFilter routines to display only those records of interest.  Both procedures Call a chgFontColor procedure that is supposed to run against only the filtered cells.  

The View_Trip_Schedule works without a problem, but the View_Hotel_Schedule ends with a Run-Time error '1004' No cells were found."  Yet after the error occurs, I can click on the AutoFilter drop-down in Cell AN8, click the already selected "Text Filters | Custom Filter", and see the criteria that was provided by the VBA.  By doing nothing else other than clicking 'OK' on the already existing criteria, the Filter then works (Instead of that, I can also just re-run the View_Hotel_Schedule procedure again and this time it works). Without knowing why this is happening (and I've tried most of the weekend to get this working), I've attempted to work around it by using the macro recorder to capture the steps I manually took (as described in the previous sentence) and then running that additional code if that Run-time error occurs.  Still no joy.

To reproduce this error, I first select the "15 FBT" ("Trip Number") value in Cell AP5 (this is the 8th item in the Data Validation list, so need to scroll the list to find that) and run the View_Trip_Schedule procedure by clicking the "Navigator" button in Cell A1, then clicking the "Select Trip" button in the Navigator Userform.  That runs without problem (Note the attached file is already in this condition as this procedure has already been run - I mention the sequence here in case it is necessary to run through this again).

I then select the "Bayerischer Hof" ("Hotel") value in Cell AP3 (this is the 3rd item in the Data Validation list) and run the View_Hotel_Schedule procedure by clicking the "Navigator" button in Cell A1, then clicking the "Select Hotel" button in the Navigator Userform.

The error occurs in the Called chgFontColor procedure in this line:

    Set filteredRng = rng.SpecialCells(xlCellTypeVisible)  '*** ERROR Occurs Here ONLY when called by View_Hotel_Schedule ***

Both of the Calling procedures serve to display only those columns with Dates (in Row 8) that correspond to either a given Trip (e.g. "15 FBT") or a given Hotel (e.g., "Bayerischer Hof") and further processing (e.g. the Called chgFontColor procedure) should occur only against the Filtered cells in those columns.  

In terms of differences between the two Calling procedures:

The View_Hotel_Schedule also has Totals for the Hotel in Rows 510:535, so there is a second AutoFilter Criteria to include Rows with "Totals" in "AN510:AN535" (Note the word "Totals" is in White font in those Cells so they are not visible).  The first AutoFilter Criteria in this procedure also uses a "Begins with" Text filter criteria because it operates against a Concatenated Field of "Hotel | Trip".  So the code for this procedure is:

    rngToFilter.AutoFilter Field:=1, Criteria1:= _
        "=" & thisHotel & "*", Operator:=xlOr, Criteria2:="=Totals", Operator:=xlFilterValues

Select allOpen in new window

... whereas the similar code for the View_Trip_Schedule procedure is simpler (because the Totals rows 510:535 are not needed and it is a straight Value comparison):

    rngToFilter.AutoFilter Field:=1, Criteria1:=Range("AP5").Value

Select allOpen in new window

Hope this is clear and someone has some insights on how I can get this working (where both procedures can successfully run).  

Jeff

EE-Question.xlsm