Thread: 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.)

Public Sub Example()
    Dim rngInterest As Range, dblTotal As Double, lngRow As Long
    Const c_row = 2 'start row
    Set rngInterest = Range(Cells(c_row, 1), Cells(Rows.Count, 1).End(xlUp)).Resize(, 5)
    With rngInterest
        dblTotal = .Cells(.Rows.Count, 4)   'total $ Variance
        For lngRow = 1 To .Rows.Count - 1   'iterate all but Total Row
            With .Rows(lngRow)
                .Font.Bold = .Cells(4) / dblTotal > 0.2
                If (.Cells(5) > 0.1 Or .Cells(5) = 0) And .Cells(4) > 2 Then
                    .Interior.ColorIndex = 38
                ElseIf (.Cells(5) < -0.1 Or .Cells(5) = 0) And .Cells(4) < -2 Then
                    .Interior.ColorIndex = 35
                End If
            End With
        Next lngRow
    End With
    Set rngInterest = Nothing
End Sub

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.