VBA To Calculate Running Total and Output Calculation

[xcellnthefuture]

Hello! I am trying to do a "Do While" loop to calculate a running total based on criteria. For the below:

What I am trying to do is while the SKU column is the same, add up all the pink quantities together, add up all the purple quantities together (make the purple quantities positive) and then output the lower number between the two on the last line of the purple for that SKU.

Here is what I did manually, that I need the VBA to do.

Here is my code that I am currently working on.

 

[rpaulson]

try this on a copy of your file.

change your color index numbers to what you have for pink and purple

Sub do_it()

'green 48
'blue 33

pinkcount = 0
purplecount = 0

For r = 2 To Cells(Rows.Count, "C").End(xlUp).Row

If Cells(r, "H").Interior.ColorIndex = 48 Then pinkcount = pinkcount + Cells(r, "H").Value
If Cells(r, "I").Interior.ColorIndex = 33 Then purplecount = purplecount + Cells(r, "I").Value

If Cells(r, "C") <> Cells(r + 1, "C") Then 'next sku

x = WorksheetFunction.Min(Abs(pinkcount), Abs(purplecount))
If x = 0 Then x = ""
Cells(r, "O") = x

pinkcount = 0
purplecount = 0

End If
Next r

End Sub

hth,
Ross