VBA multi row highlight issue

[Flower76]

Hello,

I am working in Access to export a query to Excel. The number of records varies depending on the parameters selected. If there are more than 1000 records I would like to highlight alternating blocks of 1000 records. I sort of have it working, but not quite the way I would like it. Although I understand the problem, my VBA skills aren't particularly great and I'm having trouble figuring out how to fix my issue.

The problem is that the last block is unlikely to have exactly 1000 records in it, but it's set to highlight 1000 so I need it to look at the selection and only highlight the number of filled cells. The second is that because it alternates colours it also continues 1 block of 1000 past empty cells.

Any help is greatly appreciated!!


----------------------

If rst.RecordCount > 1000 Then

Do Until IsEmpty(ApXL.ActiveCell.Offset(1, 0))

    ApXL.ActiveCell.Range("A2:C" & 1000).Interior.ColorIndex = 15
    

    ApXL.ActiveCell.Range("A1:C1").Offset(1000, 0).SELECT
    ApXL.Selection.Resize(1000, 1).SELECT
    ApXL.Selection.Interior.ColorIndex = 2
    
    ApXL.ActiveCell.Range("A1:C1").Offset(1000, 0).SELECT
    ApXL.Selection.Resize(1000, 1).SELECT
    ApXL.Selection.Interior.ColorIndex = 15

Loop

End If