Any question answered...

My macro is fugly.
It applies a conditional format to all the cells based on dates, and then for specific ranges it adds a specific cell format to that range based on the values in there.

Tiswas: I'm new here. Do what to what?

Matt: ta.

I know. I look at it and think :
"Yes, my hovercraft IS full of eels. Thankyou for asking"

It's a recorded macro rather than a proper VBA solution.

Conditional format means that each conditionally formatted cell has a rule it uses to work out what colour it should be. Each time the data in the worksheet changes, the cell will recalculate the result of the data, and its colour may change.

That could be fine, but it sounds like you are trying to set the formulas twice (or more?!), working out what colour each cell should be by a process that has several steps and visits each cell more than once. This too would be fine if each time you visit a cell you just told it what colour to be (set the format directly from the macro), but instead you are telling it what rule to use to choose its colour. and each time you tell it a new rule it forgets all the rules it was told before, and you get weird results. If the last step of your process doesn't set rules for every cell then things get even weirder as the cells get left with a patchwork of different rules from different stages of the process.

Either set the colours directly step by step, and you will have to re-run the macro each time you want the colours updated.

Or set just one huge formula per cell that combines all the things that cell has to think about to choose its colour.

Or I'd do something like: make another worksheet full of formulas: some to check the individual conditions the data have to obey, some to combine the results of those checks to work out a result for each potentially coloured cell. On the worksheet with the data i'd set really simple conditional format rules that just read the answer from the formula worksheet and set the colour.
edit-----------
Urgh: actually this is wrong, conditional formatting is more twisted than i remembered, and you should be able to give each cell up to 3 conditions to check. And now i can't see why any of my expressions wouldn't work. The example you posted with your version of mattty's expression doesn't work because he was using it in the opposite sense in his If statement, so you need to wrap it in a NOT() to make it do what you want. Some study of boolean algebra may be helpful. I got from my first expression to my third by knowing that NOT(AND(A,B))=OR(NOT(A),NOT(B)) (one of DeMorgan's i think).