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.
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.