Conditional formatting range changes when cells are merged

I am trying to setup a sheet I will use for working out staffing allocations in my school. I have a list of teachers. Below that I have, for each grade level, the collection of classes they'll need. Each class is represented by a merged group of cells, ie "Grade 12 Business Standard & Higher Level" requires 6 teaching periods, so I merged 6 cells and the cell content is "Grade 12 Bus SL/HL"

Once I have set up all the necessary classes, I will drag each set of merged cells to the appropriate teacher. This then gives me a wonderful tool I can play around with to organise the teaching loads for next year. It's awesome! I have done this before, without conditional formatting, but I'd like to go one better this time, as otherwise I get annoyed when all the borders get messed up and I have to reformat from time to time.

I have the conditional formats set up (based on cell content, ie finds the string " bus" then colours the merged cells and outlines them). And it works beautifully, and I even have formulae which work for multiple subjects (i.e. classes in the same department)

Please Login or Register  to view this content.

BUT

When I try to do this for new cells, it highlights but then only puts the border around the left-hand cell of the merged group. And that is consistent if I move the cells, and if I save and reopen the file. And when I look at the cells the conditional formatting applies to - it is no longer for the entire sheet and the range has gone all weird.

SO I fix it. I have defined a Name "Entire" which refers to the whole sheet. I replace the weird multiple ranges mess with =entire, it works perfectly, the formatting corrects, lovely.

But then I merge some more cells, and voila - the problem is back again.

I'm going to be doing a LOT of merging (and then moving) cells around on this sheet, I'd quite like this to work.

1. Is this making sense?
2. Can this be done through Conditional Formatting?
3. Would it be better done through VBA and if so, how?

I have attached a sample of this sheet if you want to take a look through it. This was created in Excel 2010.
Note, the merged cells can currently be found at row 100 onwards

Putting aside the question of the use of merged cells, if VBA was a solution, how would you "allocate" a set of merged cells to a teacher and "period".

It would suggest to me a fairly complex set of dynamic conditions/rules would need defining.

Currently it appears to be a "drag and drop" exercise based on your knowledge which is not likely to be replicated in VBA,