Jun 01 2022 11:31 AM
Jun 01 2022 11:31 AM
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.
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.
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.
Jun 01 2022 12:45 PM
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 > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula =SUM(D$1:D1)<80000
Activate the Fill tab.
Select a color.
Click OK, then click OK again.
Jun 01 2022 01:28 PM
@Hans Vogelaar 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!!!
Jun 01 2022 01:57 PM
I appreciate that @Hans Vogelaar 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.
Jun 01 2022 02:06 PM
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
Jun 01 2022 02:07 PM