excel vba - VBA script for managing inventory levels -

i have been trying quite time make interactive worksheet tracks inventory levels , kicks in production when drops set lower bound value.

for example, given on 24 hours consumption rate 10 items per hour starting inventory of 100 , minimum stock level of 20. hits 20, want excel automatically kick in production mode , produce @ specific rate (formula) {example: @ rate of 20 items/hour). during time consuming, net increase in stock 10 items/ hour. reach upper bound should stop production , continue downwards count.

i have production color coded blue , consumption red. vba script reads font color , determines how many people need produce @ given hour (sum of cells blue text).

i have different production rules, example, small job produced 6 hours , repeated after 22 hours. based on consumption rate. need vba can automatically fill in schedule based on set rules. currently, fill in cells according these rules while manually keeping total production hours below given constant (sum of cells blue text)

5 large jobs: (run time: 14 hours gap time: 8 hours) per job
10 medium jobs: (run time: 10 hours gap time: 13 hours) per job
20 small jobs: (run time: 6 hours gap time: 22 hours) per job

thank reading entire problem!

the vba using reading blue font color is:

public function countcolour(prange1 range, prange2 range) double  'update 20140210 application.volatile dim rng range each rng in prange1     if rng.font.color = prange2.font.color         countcolour = countcolour + 1     end if next  end function    

excel-vba

share|improve question

edited mar 28 @ 14:07 shai rado 22k51233

asked mar 28 @ 14:00 oas 11