Excel Macro help if Red cell values are there then compensate that value with Black cell values

[Jyotirmaya]

I have two sheets, Sheet1 and Sheet2, There are 3 categories in my sheet A category, B category, C category. Under each category there are 5 sub categories and they are 1st year, 2nd year, 3rd year, 4th year, 5th year. In a column for example I have some values and I have the sum of the values if in a column there are values in Red then that will be consider as Excess amount collected values and that I need to compensate against the same column values in same category. For example in my example I have 35 in excess in SL row column A 103 and I need to compensate that 35 among 100,101,103,104 SL row hence it should start from lesser values such as It will first choose value 4 of B7 to compensate then value 5 of B6 then value 10 of B3 then the Value 20 of B4 but the Black sum amount is 39 of B column and excess amount is 35 so it can compensate only 35, so after compensating 4+5+10 it will compensate 16 from from B4.


Similarly in a column if there are excess values more than 1 then it will start compensating from the highest amount such as in my example there two excess amount in Column C in 2nd year there are two Excess amount 20 and 50 in Sl row 101 and 102, but the Black sum is 19 and excess sum is 70 so 50 is compensated and rest excess balance amount is there 31 after compensating.


What should be the code to make this in sheet2 from Column B to column P, I have 2000 rows so the TOTAL in Black and Red is there in A2001 & A2002 and the sum in Black and Red are already there.

Sheet1

Sheet 2