incremental sum with range that varies

[izzy117]

I am trying to sum a sales forecast and highlight the month when my inventory will run out. I have months as headers with numbers in each row and the last row being my current inventory. Given the sample below i would want to highlight the cells in column D (mar-11) because the sum of Jan+Feb+Mar is > Inventory.

As stated in the title the range will vary starting in Feb-11. I am able to locate the column of the current month with the following
mycolumn = Cells.Find(What:=(WorksheetFunction.Text(Now, "mmm-yy")), After:=ActiveCell, LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
I take no offence in being informed of how amateur that line is

Part No. .....jan-11...feb-11...mar-11....apr-11...inventory
Item 1..........100......300.......400.........100.......775
Item 2..........300......500.......500.........400.......1,000


I am trying to avoid making a ton of if statements like...
if sum(mycolumn, mycolumn.offset(0, 1)) > Range("F2").Value then
do mycolumn.offset(0, 1) highlight
if sum(mycolumn, mycolumn.offset(0, 2)) > Range("F2").Value then
do mycolumn.offset(0, 2) highlight

I am sure this will require a loop but i've never exicute one like this before.
Any help would be appreciated.

 

[Scott Huish]

Why not use Conditional Formatting?

 

[izzy117]

EDIT: Does the conditional formatting add a lot to the file size? Say if i were to just leave it on 1000 rows?



simple answer: The range of ALL the work varies wildly.

I'm running a macro to copy and paste customer informationn onto the sheet with all their information as sampled above. Then do a loop to gather bill of materials information for each of those part numbers and then pull out unique rows of materials and sumif the columns for those given materials. Depending on how many different part numbers there are my work can start on A:20 or A:400.

 

[izzy117]

Users do not want me to dump last months numbers. They needs to see them all year long.

next month (Feb-11) i want to sum(Feb+Mar+Apr) and finding that the sum is greater than inventory, highlight apr-11.
Part No. .....jan-11...feb-11...mar-11....apr-11...inventory
Item 1..........100......300.......400.........100.......775

In 2 months (Mar-11) i want to sum(Mar+Apr) and finding the sum is less than inventory, highlight nothing.
Part No. .....jan-11...feb-11...mar-11....apr-11...inventory
Item 1..........100......300.......400.........100.......775

 

[izzy117]

This is what i am using. Is there a smarter way of doing this?

Sub SumOfMonthsGoingForward()
'ignoring data from previous months
Dim mycurrentmonth As Range
myinventory = ActiveCell.Offset(0, 47).Value
mycolumn = Cells.Find(What:=(WorksheetFunction.Text(Now, "mmm-yy")), After:=ActiveCell, LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column - 2
Set mycurrentmonth = ActiveCell.Offset(0, mycolumn)
If mycurrentmonth.Value > myinventory Then
mycurrentmonth.Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 1))) > myinventory Then
mycurrentmonth.Offset(0, 1).Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 2))) > myinventory Then
mycurrentmonth.Offset(0, 2).Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 3))) > myinventory Then
mycurrentmonth.Offset(0, 3).Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 4))) > myinventory Then
mycurrentmonth.Offset(0, 4).Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 5))) > myinventory Then
mycurrentmonth.Offset(0, 5).Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 6))) > myinventory Then
mycurrentmonth.Offset(0, 6).Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 7))) > myinventory Then
mycurrentmonth.Offset(0, 7).Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 8))) > myinventory Then
mycurrentmonth.Offset(0, 8).Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 9))) > myinventory Then
mycurrentmonth.Offset(0, 9).Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 10))) > myinventory Then
mycurrentmonth.Offset(0, 10).Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 11))) > myinventory Then
mycurrentmonth.Offset(0, 11).Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 12))) > myinventory Then
mycurrentmonth.Offset(0, 12).Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 13))) > myinventory Then
mycurrentmonth.Offset(0, 13).Select
GoTo 1
Else
Exit Sub
1:
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.599963377788629
.PatternTintAndShade = 0
End With
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub