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

Question

Hi All,I have a living worksheet that is updated anywhere from weekly to daily. I have a column that contains only currency values, and I need to sum the column from the top, and stop once I hit a certain number, and color those cells a specific color.&nbsp;For example, there are 100+ rows of data, 26+ columns, one column is all currency values, I need to sum it, from the top, until I get to $80k and create a conditional format that will highlight only those cells a certain color, and will update itself as rows are added and deleted manually.&nbsp;&nbsp;&nbsp;Please note that I am fairly an Excel noob/self taught off Google and YouTube, so you might have to walk me through this, if its possible.&nbsp;

hansvogelaar · Answer

BrianWW&nbsp;
Let's say the currency amounts are in D2:D150.
Select this range. D2 should be the active cell in the selection,
On the home tab of the ribbon, select Conditional Formatting &gt; New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula =SUM(D$1:D1)&lt;80000
Click Format...
Activate the Fill tab.
Select a color.
Click OK, then click OK again.

brianww · Answer

HansVogelaar&nbsp; 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!!!

mathetes · Answer

BrianWW&nbsp;&nbsp;I appreciate that&nbsp;HansVogelaar&nbsp;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.&nbsp;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 &nbsp;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?&nbsp;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.

hansvogelaar · Answer

BrianWW&nbsp;
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)&lt;160000, a rule for =SUM(D$1:D1)&lt;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(" &amp; col &amp; "$" &amp; fr - 1 &amp; _
                ":" &amp; col &amp; fr - 1 &amp; ")&lt;" &amp; i * amount).Interior.ColorIndex = i + 2
        Next i
    End With
End Sub

brianww · Answer

Thank you for the reply! The spreadsheet is used to track production in a manufacturing company that does custom work. It contains a lot of info about what product is being produced, who is building it, where's its at in the process, the invoice value, ship dates, and more. There's already a fair amount of conditional formating in place to highlight orders in relation to how close they are to their ship dates.The specific change I'm making is so our shop manager can easily see how many orders there are in a given dollar range, relevant to those units that are closest to needing to ship, so that he can better track and hit his shipping goals ($ per week).I manually update this report at least once a week, deleting orders that have shipped (moving them to another sheet), and adding all of the new orders since the last update. It's a shared document with our company's leadership team to manage and organize our production.

Forum Discussion

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

Share

Resources