VBA:

[mykulpasskwa]

Hello again, I'm stuck on a problem that I can't even figure out how to get started, and it's been a few months, so any help would be appreciated. Below is a sheet I'm working with and the goal is to highlight cells that go over an annual cap. As you'll see the terms start with a four digit year (2019, 2020), and each year the cap is different. So for 2019 lets say the cap is $25,000 the person reached their annual cap in 201930. In 2020 the cap was $26,000 so they didn't reach their cap until 202040.

I think what I need to do is have excel check if there is a value in column B and column F, then find cells with the same year, then add those cells together, and then check if it's over the annual cap. If it's over the annual cap then only that cell that went over is highlighted yellow.

The purpose of this is so that the user has an indication they've gone over the cap in that term and need to provide a different funding source. I don't want to actually display the Sum of the value anywhere, at least not yet, but I just want the user to be aware of it. I've been messing around with some scripts, but they aren't quite achieving what I want and I'm not strong enough in VBA yet to hash this out on my own. Hope one of you would be able to help me, and please let me know if you need more information.

 

[mikerickson]

to get the sum for 2019, you could use =SUMIFS(F:F,FB:B,">201899",B:B,"<202000")

The highlight over the cap part would require that there be a list of caps and years somewhere. Where are they and what format are the years?

 

[mykulpasskwa]

Well the thing is I need to get the Sum for every year the student has been enrolled. So I need to find 2008-2013 for some students, 2016-2020 for other students, and all different combinations for every other student. What I just provided is a sample to get me started.

I have the caps in descending order for 2015 - 2020 starting on the same sheet in B26:C31 range.

Edit: the caps are in currency, but it doesn't need to be currency.