VBA call function based on change and bg-color

Goal:
To create an interactive savings week schedule where i can alter start/end week, and goal amount. The background will be formatted with blue background and the amount average per week will be added into each cell where it is applicable.

Example (as picture):
We have a goal amount of 1000 pounds for a bike. We will try to save money between week 6 and 10 which totally is 5 weeks. Average is 200 pounds per week, which is displayed in the table with blue background of every cell with an amount.

Status:
I made the background change with conditional formatting so when i entered week_Start and week_End I will get the blue area as in the picture. What is still missing is the average amount per week.

Idea:
My idea would be to keep the conditional formatting and when any of the numbers within the red border are changed, the function will return a value, amount_Goal/(week_End-week_Start+1), for each cell in the schedule with that specific bgcolor. How would this be done in VB? I have tried for hours.. or is there any other better way to solve it, and if so, how? If it is possible, i would like to be able to add more savings goals underneath the one in the example to be applied for different tasks. My VB knowledge is extremely limited, just started with VB so all help is appreciated.

Thanks, V

savings_vba2.png

Originally Posted by JohnTopley

Formula:

=if(and(h$2>=$b3,h$2<=$c3),$e3,"")

CF

=LEN(H3)>0

Thank you so much, works like a charm!!

Originally Posted by AliGW

Administrative Note:
Is your forum profile up-to-date and showing ONLY the oldest Excel PRODUCT that you need this to work for? Latest isn't a thing

Hello and thank you! You are correct, I will update my info!