Sum a currency column, stop at a certain value, change color

BrianWW 

That'll take a lot of work, since you have to create a separate rule for each tier.

You'd have to create a rule for =SUM(D$1:D1)<160000, a rule for =SUM(D$1:D1)<240000 etc.

Finally, reverse the order of the rules in the Manage Rules dialog, so that the one for 80000 is at the top, then the one for 160000 etc.

If you wish, you and run the following macro to create the rules automatically. Change the constants at the beginning to suit your situation.

Sub CreateRules()
    Const col = "D" ' column to color
    Const fr = 2 ' first row
    Const amount = 80000 ' threshold
    Dim rng As Range
    Dim s As Double
    Dim n As Long
    Dim i As Long
    Set rng = Range(Cells(fr, col), Cells(Rows.Count, col).End(xlUp))
    s = Application.Sum(rng)
    n = Application.RoundUp(s / amount, 0)
    With rng.FormatConditions
        .Delete
        For i = 1 To n
            .Add(Type:=xlExpression, Formula1:="=SUM(" & col & "$" & fr - 1 & _
                ":" & col & fr - 1 & ")<" & i * amount).Interior.ColorIndex = i + 2
        Next i
    End With
End Sub

BrianWW 

I appreciate that HansVogelaar was able to answer your first question. My guess is that a variation on that first response would work: that is to again select the entire range but now have multiple conditional formatting rules--all entirely possible within the Conditional Formatting dialog box--with the first color for values between 0 and 80, the next between 80.01 and 160, the next between 160.01 and 240, etc.

HOWEVER, you mentioned in your first post something to the effect that that rows and manually added and deleted and you want this system to keep up automatically with all that. It probably will, but...BUT..this is all starting to sound like a Rube Goldberg machine to me. I wonder if you could be so kind as to back up and describe the big picture, so long as we're not violating some kind of privacy or corporate security. WHAT exactly do all these rows represent (we know the column in question contains  currency figures)...What does each new row represent? Why do some old ones get deleted, new ones added? And maybe most important, what is the significance of the highlighting?

I ask because it's entirely possible that there's a more elegant solution, a less cludgy way to get from point A to point B.