Forum Discussion
Sum a currency column, stop at a certain value, change color
HansVogelaar Awesome, thank you for your reply, it worked! Next question, is there away for me to start summing the remaining cells, stopping again at $80000, setting the color, and essentially repeating this through the entire column, so that every $80000 worth of cells is it's own color? Thank you!!!
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