Conditional Formatting VBA: Need More Than 3 or Keep Checking After Condition Met

I'm banging my head against the wall on this and I really appreciate any help anyone can give me!!! Hopefully I can explain this clearly but please ask questions if needed and I'll try to clarify.

I am trying to write some VBA for conditional formatting on a range of cells based on multiple relative cell reference formulas and I've run up against a few issues. The numbers in the cells will not change so I don't need a Worksheet Event. I just want to run a macro each time I create a new report.

I want to make each row a color and/or bold font if it meets certain conditions (example attached, can't use actual data due to sensitivity):
Bold Font if the individual account $ Variance is more than 20% of the total $ Variance. Beta (row 3) meets this criteria.
Pink interior cell color if the account $ Variance is greater than 2 AND if the % Variance is greater than 10% or if it is zero. Alpha and Beta (rows 2 and 3) meet this criteria.
Light Green interior cell color if the account $ Variance is less than -2 AND if the % Variance is less than than -10% or if it is zero. Delta (row 5) meets this criteria.

Issues result because a row can be both bold and a color, or just bold, or just a color. Issues:
1. Excel stops checking conditions after one is met (but a row might be bold and color). Potential Solution: I could use regular conditional formatting, and accomplish my goal in 3 conditions, if I could get Excel to continue checking the conditions after the first condition was met. I can't figure out how to do that (Excel 2003).
2. Excel won't allow more than 3 conditions. Potential Solution: Using the Select Case function. However, I can't figure out how to use relative cell references on a range of rows, based on a single cell in each row (or a formula involving a couple cells).

Using solution 1, my conditions would be:

A2:E2= Bold =$D2/$D$7>.02
A2:E2= Pink =AND(OR($E2>0.1,$E2=0),$D2>2)
A2:E2= Light Green =AND(OR($E2<-0.1,$E2=0),$D2<-2)

Using solution 2, they could be the same as above, or if necessary, they could be:

A2:E2= Pink & Bold =AND(AND(OR($E2>0.1,$E2=0),$D2>NamedRange*2),$D2/$D$7>0.2)
A2:E2= Light Green & Bold =AND(AND(OR($E2<-0.1,$E2=0),$D2<-NamedRange*2),$D2/$D$&>0.2)
A2:E2= Bold =$D2/$D$7>.02
A2:E2= Pink =AND(OR($E2>0.1,$E2=0),$D2>NamedRange*2)
A2:E2= Light Green =AND(OR($E2<-0.1,$E2=0),$D2<-NamedRange*2)


Here is an example of my data and what it would look like if I could figure this out (attached):

VBA Issue.JPG

Not entirely clear if the no. of rows will adjust with each run... have assumed so but that column references are fixed (i.e. $ Variance always Column D for ex.)

Please Login or Register  to view this content.

Notes:
- In the narrative you state clearly >2 etc yet your sample results imply >=2 ... modify operators nec.
- the above also assumes a "clean" slate when commencing so there is no clearance of existing bold font and/or interior fill - this is straight forward to add if required.