Skip hidden rows with offset

I have attached a sheet which is a much simplified version of the issue I am having.

For the presentation of a report, I want alternate groups coloured differently, and each group consists of two rows. I have set up the values in column B as an OFFSET from the reference points in rows 1-4 (and associated conditional formatting to them) so that I can insert extra two-row groups and maintain the red-red/blue-blue pattern throughout the list.

The problem I have arises when you filter column A. For the purposes of the report I want to maintain the red-red/blue-blue pattern throughout, but as you can see this does not work as the OFFSET function recognises hidden rows.

So what I want to know is: is there a way to use OFFSET so that it ignores hidden rows and only looks for the specified reference from visible rows?

I have searched the web and found a couple of solutions that require using a loop through VBA script, but I am trying to avoid this as the actual document I need this for is 1000+ rows grouped into 5 sectors and loops on it take an age.

Thanks in advance for any help...

Originally Posted by DonkeyOte

Not entirely sure I follow

Sorry, tried to put together a small snapshot of a much larger project to demonstrate the problem and it looks a bit pointless.

I have used your soloution in the main project and it works perfectly and quickly. Learned a couple of new bits there. Thanks very much, you are thankfully not Quixotic in response!