Formatting unknown ranges based on contents of a column

Hi all,

i can't seem to figure this one out.

I attached a file showing how i am trying to format data (the second tab).

The big issue is the counting to format. (the formatting itself is obviously easy.)

The number of columns is not always standard so i need to count for it.

the number of rows in each "room number" is also not always standard, which means i also need to count for those.

If anyone has had experience trying to do something like this i would appreciate the help.

i know it is possible because i had one that someone had given me years ago, unfortunately i lost it in a hard drive failure...also years ago.

if it isn't obvious what i am trying to do, let me know and i will try to be clearer.