How can I get rid of loads of conditional formatting
 

I have a workbook for processing exam scores, calculating weighting between exam areas, and working out final grades etc. It's nothing fancy or complex. Has a lot of nested If's but worked fine until today.

Stupidly, I put in a ton of conditional formatting today to prevent data entry errors as a way of validation. Doh, it now runs like a dog, like 20 seconds at 98% CPU to copy paste 1 cell. But the file size is just 275kb.

1. How can I get rid of, or reduce the conditional formatting. Tried the CF Manager, but there are now '000's of cells, so not an option.

2. What is the simplest way to prevent or warn the user that they have entered a number in 1 cell that results in 100% calculation in the adjacent cell? Currently I had just put CF on the calculated % score result cell. Worked fine, until I extrapolated across the board by way of mass Format Painter.

Help...

Thanks in anticipation.

You can select all cells with conditional formatting via:

F5 Special Conditional Formatting OK

Why not try a formula that looks like this:

=IF(Reference Cell = "the thresh hold", 100%, Some range %)

or create a table like:

A_____B_____c
1 10____20____10%
2 21____30____20%
3 31____40____30%

Then use a VLOOKUP function like: =VLOOKUP(Reference Cell,A1:C3,3,1)