So far, as I see it, all of this is do-able.
I'll be looking at this on and off over the next few days.
In the meantime, a few questions:
1. The results sheets all currently have exactly the same layout; everything in Column A (apart from the name in A1) is exactly the same and in the same cells. Is this set in concrete? It makes coding very easy if it is set in concrete because we know where things will always be, but a little less robust should things move about.

2. I see you've put some work into supplying the RGB values of colours; is this an industry standard type of thing or just the colours you like? I can (and it's easier) use the actual colours you've used to fill the cells in columns B & C in the Reference sheet, that way, you can tweak the colours in that sheet in the usual way until it's visually pleasing, and the code will use those colours on the results sheets. [The same RGB value in different applications often leads to very different visual colours - even within the MS Office suite!]
I'll assume you're happy with copying the colours used on the reference sheet to the results sheets (ignoring the cells with RGB numbers in)… unless I hear different from you.

3. How constant is the placing of data on the Reference sheet? Is it set in concrete?
Is there the possibility of new tests being added, more or fewer ranges within an existing test? etc.



I intend to use thresholds instead of ranges. So I won't be checking if a given value falls between values in column C and values in column D, I'll only be using something like either column B or column C to determine which range a value sits in. It's easier logically and practically to use thresholds, that way there is no danger of a value not being categorised (For example, I see Haematocrit results come with one decimal point. Let's say you receive a value of 10.3, will that go into the Critical band or the Very Severe band? Currently it falls outside both. The same problem arises with a value of 19.6). Using thresholds means you don't have to worry about checking that you have contiguous bands. Perhaps you'd like to put your mind to updating me on what those thresholds are.
For BUN, you currently have:
Low: 0 to 15
Normal: 16 to 37
High: 38 to 120

Nowhere for 15.5 to go, nor 37.2.
What I'd like to see instead is the likes of:[see next msg. too]

0
Low
16
Normal
37.5
High
120

Then I'll rearrange that into something the code (formulae too, btw) can use.
(btw, what happens above 120? Is it still 'High'? In which case do we need 120 at all? Anything above 37.5 would be 'High')