Use the Sheet calculate Event and hard code the ranges

Could you kindly provide a working example sub incorporating the above? A
modification based on the example sub posted would be great

No need for VBA IMO, you should be using Conditional Formatting as this will
be dynamic with formulas or hard values

Agreed. But it was just a short example sub in the query posted, the actual
coverage was for 8 different fillcolors (1 - 8), far in excess of CF's 3 (or
3+1) colors