Conditional Formatting from a different sheet to highlight specific ranges

Hey Beamernsw

Sorry I didnt explain it very well I was mentally exhausted from looking at it all day and the values I am dealing with are in the 1000s of tools and over the past two years.

So if you see along the top it has different aspects of the job process. The values I have explain what the job parts are much better but are to sensitive to post on the internet due to security issues.
Another issue is that the dates I have used in the sample are a day apart when in reality the job parts can be days weeks or months apart so take that into account. The rules I will be making will sit on top of the job start-job finish highlighting. Adding the worker isnt actually needed now that I spent more time looking at it. I can just add a header and sort via that instead. So What I proceeded to do was do an and function on sheet1. This function checks the first date at the top "H£" then checks the the job start of the first job on sheet 2 "sheet2!I2" then checks sheet1 H3 again and compares it to the Job finish sheet2V2.

Once I have the start and the end working I can work on the parts in between job part 2-4 will be clear, job part 5-6 will be yellow also with some text, Job part7-10 will be red and job part 11-finish will be yellow again.
This is the function I have made in the formatting "=AND(H$3>=Sheet2!$I2,H$3<=Sheet2!$V2)" but I am not sure if this is the best way to do this. I will have to replicate this 2000+ times for a period of 2 years and sheet 2 values will be updated each week. I was thinking I would have to use something different since they are dates but im not sure if excel will just class all dates of this format as numberic values eg 42012 = 08-01-2015.
smaller sample updated.xlsx

Hey Beamernsw

Sorry I didnt explain it very well I was mentally exhausted from looking at it all day and the values I am dealing with are in the 1000s of tools and over the past two years.

So if you see along the top it has different aspects of the job process. The values I have explain what the job parts are much better but are to sensitive to post on the internet due to security issues.
Another issue is that the dates I have used in the sample are a day apart when in reality the job parts can be days weeks or months apart so take that into account. The rules I will be making will sit on top of the job start-job finish highlighting. Adding the worker isnt actually needed now that I spent more time looking at it. I can just add a header and sort via that instead. So What I proceeded to do was do an and function on sheet1. This function checks the first date at the top "H3" then checks the the job start of the first job on sheet 2 "sheet2!I2" then checks "sheet1!H3" again and compares it to the Job finish "sheet2!V2".

Once I have the start and the end working I can work on the parts in between job part 2-4 will be clear, job part 5-6 will be yellow also with some text, Job part7-10 will be red and job part 11-finish will be yellow again.
This is the function I have made in the formatting "=AND(H$3>=Sheet2!$I2,H$3<=Sheet2!$V2)" but I am not sure if this is the best way to do this. I will have to replicate this 2000+ times for a period of 2 years and sheet 2 values will be updated each week. I was thinking I would have to use something different since they are dates but I am not sure if excel will just class all dates of this format as numberic values eg 42012 = 08-01-2015.
Attachment 372254

Sorry Bengy, I've been away and won't be here much over the next few days again either.
I modified your conditioning slightly and duplicated it 3 more times.

So the 4 conditions are:-
If cell is equal to or greater then Job Start AND less then Job Part 2 then yellow fill.
If cell is equal to or greater then Job Part 5 AND less then Job Part 7 then yellow fill.
If cell is equal to or greater then Job Part 7 AND less then Job Part 11 then red fill.
If cell is equal to or greater then Job Part 11 AND less then or equal to Job Finish then yellow fill.

I think that matches this:- (except I didn't know what colour to start with so I left it yellow)

Once I have the start and the end working I can work on the parts in between job part 2-4 will be clear, job part 5-6 will be yellow also with some text, Job part7-10 will be red and job part 11-finish will be yellow again.

I have it looking as far as row 3000. If you go over that you'll have to increase the range.
I hope this is somewhat close to what you were wanting.