A few questions relating to your uploaded workbook:

You have 3 worksheets ("1 lane out", "hard shoulder", "2 lanes out"). Can we focus exclusively on the "1 lane out" worksheet at least to begin with? Or are there inter-dependencies?

Can you confirm that the goal is to populate the table in the upper right of the "1 lane out" worksheet (in the range H3 to J5)?

Am I right in thinking that the intent is to determine whether there are overhead cables between the "Access" point and the "End of works?

If so, then in your data there is only one "End of works" entry (cell B14 = 23.7), the corresponding "Access" is at 19.9. There are cables at 20 (are these all miles?) therefore your table should look as follows:

roadworks.png

If I am right, can you extend your sample with a few more data points - I'm still far from sure that I have enough of the story to provide a full answer.

If I'm on the wrong track then please manually calculate and populate a few rows of your table and explain, in words, how you derived your table entries from the provided data.

On a side track, I happened to notice that you have about 50 separate conditional formatting rules for cell B10 and another 50 for cell B11> I can't glean what you are trying to do, but if you could explain, then I'm sure there is an easier way. This is a secondary issue, so don't bother with it unless it's a quick and easy answer.