Format Painter in a Macro

[sharpeye]

Hi guys, Im having an issue I hope someone can help me with

I have my spreadsheet (which is a work of art) but I have noticed an issue using too many conditional format rules in a single cell. When I have just checked the desination cell for the format paint, instead of copying my conditional format rules once, its copying all the rules for each cell multiple times. Makes me wonder if it would copy the conditional format rules once each instead of multiple times, my excel would stop crashing.
Im using Excel 2007.

I have my row number 1 which is my conditional formatting template row and my row number 2 is the data id like to apply conditional formatting to. I run a macro to sort the data im using into the relevant cells and once all the data is in place the macro highlighs row 1, where one of my cells for example has 3 conditional formating rules, 1 to paint the cell red, one for green and one for yellow and then copies those formats onto my live data but instead of just copying once, its pasting the formatting rules 3 times. This becomes very memory intensive with the amount of data I am working with and eventually when the macro tries to paste the raw data into the relevant cell, I get an error about pasting the data into the cell, then excel hangs and then I loose any unsaved changes and have to start again.

Is there anything I can do to stop the format painter applying these rules multiple times??
Many Thanks

 

[sharpeye]

As part of what I hope to do if I can get this issue resolved is to apply a lot more conditional format rules to a single cell. I understand there are limitations to the number of rules I can apply so I hope to find an alternative method.
What I'd like to do is for example

if M1>90% paint the cell yellow
If M1=89% and N1>=1.5 paint the cell yellow
If M1=88% and N1>=1.86 paint the cell yellow
If M1=88% and N1<1.86 and N1>=1.4 paint the cell red

and so on. I know theres no way I can have as many conditional format rules as I would like, especially using 2007
Is there a different way I would be able to do something along these lines?