Conditional Formatting based on Sum

[AlanaT]

Hi all,

Thanks in advance for any help!

I have a pivot table that has Item Location and Qty on Hand, grouped by Item and sorted descending by Qty.
I am trying to consolidate the locations that have a small amount into a single location.
What I want is to highlight within each (item) group from the smallest quantity and up until the sum of these locations is equal to or less than the maximum
(which is the first item in each group). The challenge is each group may contain different number of locations (rows).

Sample attached.

[Eastw00d]

Hi AlanaT,
for the first group your function in the conditional format = =SUM(C2:C$14)<=C$2 and copy this format to the end of the group
for the second group it is: =SUM(C16:C$51)<=C$16
and third: =SUM(C53:C$91)<=C$53

Cheers
Eastw00d

[AlanaT]

Hi Eastw00d,

Thank you for this help! Does this require that I enter this conditional formatting (a different formula) for each group?
My upload was only a sample. My actual data file has hundreds of groups. Is there a way to do them all in one step/Macro/other?

[JohnTopley]

Please Login or Register  to view this content.

[Eastw00d]

@JohnTopley: a very nice solution!

Cheers
Eastw00d

[AlanaT]

@JohnTopley

This is fabulous! However, I keep getting a runtime error and when I hit debug this line is highlighted: Range("C" & srow).Resize(lr - srow, 1).Interior.ColorIndex = 20
I think the problem exists if the "group" has only 1 or 2 locations, as there aren't enough to sum to compare to the max??

[JohnTopley]

I did a test with 3 locations with values of 420/200/20 and it highlighted 200/20 but no error. You will get an error if there is only ONE location.

Please Login or Register  to view this content.

[AlanaT]

@ JohnTopley

This works!! I still get the error, but now when I debug, I just move the arrow to the "On Error Resume Next" line and continue (F5). Works like a charm. Thank you!