3+ Conditional Formatting Rules for cell range based on Formula from other cells

Hi,
I've spent a few days trying to figure out how to apply conditional formatting via VBA to a range of cells based on input from another range of cells. Obviously this would be easy in Excel 2010, but I'm still using 2003 at the office and it needs to stay in this format to be readable by other users:

For cells M8:EK8, my conditional formatting
condition 2: Formula Is =AND($E$8>=M2,(($E$8-$D$8)>=(N2-$M$2))), color index is 40
condition 3: Formula Is =AND($F$8<=M2,$G$8>=M2), color index is 39
I want to add:
condition 4: Formula Is =AND($H$8<=M2,$I$8>=M2), color index is 40
condition 5: Formula Is =AND($J$8<=M2,$K$8>=M2), color index is 39
and so on

The cells in the range M8:EK8 are blank, they only get colored based on input added to D8 to K8. If there is no input, then the cells should be uncolored.

resource tracking ex.jpg

Any help is greatly appreciated!

-Sarah

hi Sarah, wlecome to Excelforum, you could use the following formula:

OR(AND(cond1,cond2),AND(cond3,cond4)) then color index is 40

where:
cond1: $E$8>=M2
cond2: ($E$8-$D$8)>=(N2-$M$2)
cond3: $H$8<=M2
cond4: $I$8>=M2

Same for another set of 4 conditions

PS. As it is Excelforum it would be logical to provide mock up of a workbook. You would have much more chances to get replies

Thanks watersev,
I'm unsure how to translate this formula into VB wording. I can't use conditional formatting as I only get 3 rules
Inst Use Tracking ex.xls

watersev,
I would like to add your code does work if I keep to two colors. I'm completely silly and just realized I didn't specify one of the code lines to be a different color-index (say 38 instead of 39 or 40). But if I want each formula to yield a different color, then see my previous question.

And thanks again for your help!

Excel 2003 has limitation of 3 conditions for CF. My suggestion was to join conditions under the same color code and thus avoid using VB instead of CF. If your first post request is correct my suggestion should work.
In the meantime I've made a sample for 4 colors condition, please check attachment, insert start and end date. When both dates are entered and end date is later/equal than/to start date you should get proper highlighting

Bob Phillips has a free add-in, CFPlus, that you can download from his site here:

http://www.xldynamic.com/source/xld.....Download.html

This gives you up to 30 conditional formats per range, in up to 100 ranges, so it might be worth having a look at that.

Hope this helps.

Pete