Locking conditional formatting

Hi,

I am attempting to make a production schedule in excel with the use of conditional formatting.

The issue arises with the fact that I need to be able to re-order the jobs at anytime and need the conditional formatting to remain fixed.

I have built and rebuilt the book many times changing the way I protect it and it never fails to mess up once I close the book and reopen it.

I have all the cells formatted to 'Locked' and protect the sheet to the point that they are not able to even select a cell.

The schedule is set up to hold up to 98 jobs at one time. The most common origin of the problem comes from just above ov below the last job scheduled.

Is there any way to absolutely lock the formatting so it will not move in the schedule sheet when I rearrange the job order in a separate sheet?

Thank you
Jessica

It is set up to be between the start and stop time/date. Each cell in the schedule is filled with a time/date.

So for this case it is between $s$4 and $t$4.

Test.xlsx

I have attached a test book. But I'm not sure how helpful it will be. The whole thing runs on auto updates and macros which are too time consuming to recreate...

The data in the real book gets automatically updated via microsoft query so the number of items changes on a daily (even hourly) basis. I think this is what causes the errors.

The errors don't occur every time, or on every schedule (there are 16 in the real book). But they do occur in at least one schedule each time I open it.

Thanks for your help

I guess a better explanation is there isn't always going to be the same number of rows.

I need the ability to add and delete rows without the formatting scrambling.

If you try to delete and add the rows in the 'Line 7' sheet you may get the errors..

What would that formula do?