Vba loop help for cumulative addition

Hi I require help in creating a loop procedure
My data starts from cell a5
The loop should start from cell b5, add values of b5 and c6, next add b5,c5,d5 to a different sheet until no of times I specify
This should happen until last used row in a5

Attach a file with sample data and a mockup of what you want the result to look like.

What is your level of expertise in VBA?

A couple of questions:

A) How does the logic for the USA row work? You have only highlighted the first 4, but those initial 0s should pull the cumulative average down to where all of the entries in the USA row are highlighted. Your logic is not clear to me.

B) Are you required to use VBA for this? It seems to me like it would be simpler to use conditional formatting with an appropriate formula. https://support.office.com/en-us/art...b-f1951ff89d7f In your sample spreadsheet I:
1) Selected F5:I10
2) Conditional formatting -> New Rule -> Use formula.
3) Formula is: =F5>AVERAGE($D5:E5) [note the mix of relative and absolute references] Format is: Red text.
That highlighted all the same values -- except for the USA row as mentioned above. Even if I am misunderstanding your desired logic, the same approach should work -- we just need to figure out exactly what formula logic you need.

I guess the big question at this point if if you are required to use VBA for this, or if you can use conditional formatting.

This info is from pivottable, assuming there are weeks in column I would like to start from week 3, where week 3 needs to sum values of week 1 and 2. If week 3 value is more average of week 1 and 2 then week 3 should be red
Continuing on week 4, this needs to add values from week1,2,3 and if week 4 is more than avg of these three week 4 should be red
Like this goes on till last column

If I understand, this is not what you have done in the USA row:

D column: average(0,0)=0 -> 4 is greater than 0 -> so text is red (which it is).
E column: average(0,0,4)=1.3 -> 3 is greater than 1.3 -> text should be red, but you have not marked this as red. Why?
F column: average(0,0,4,3)=1.75 -> 7 is greater than 1.75 -> text should be red, but you have not marked this as red. Why?
G column: average(0,0,4,3,7)=2.8 -> 8 is greater than 2.8 -> text should be red, but you have not marked this as red. Why?

Do you understand my confusion about the exact logic you are trying to use?

I don't understand why a variable number of weeks would prohibit you from using conditional formatting. You should just need to expand the selection and include appropriate conditions. I:
1) Selected F5:BD10 (assuming you would never need more than 52 weeks total)
2) Conditional formatting -> New Rule -> Use formula.
3) Formula is: =AND(F$4<>"Grand Total",F5>AVERAGE($D5:E5)) [note the mix of relative and absolute references] Format is: Red text.

Again, this highlights all of the same numbers -- except for the misunderstanding around the USA row.

That was my first thought, which is why I asked about the pivot table. IME if your pivot table changes the number of rows and columns, it wipes out any conditional formatting that had been applied.

I -- perhaps in error -- got the impression that it was copied from a pivot table but not trying to apply to the actual pivot table. That could change things.

Edit: OP seems to have confirmed that he is trying to apply the formatting to a copy of the pivot table, not the actual pivot table.

As noted, I selected the entire 50 column block, so I did not need to do this for each column separately. A lot of times, if you pay careful attention to relative and absolute referencing, your formula/format can apply to the entire range. That is why I tried to draw attention to how I used relative and absolute references for this formula.

Originally Posted by MrShorty

As noted, I selected the entire 50 column block, so I did not need to do this for each column separately. A lot of times, if you pay careful attention to relative and absolute referencing, your formula/format can apply to the entire range. That is why I tried to draw attention to how I used relative and absolute references for this formula.

Will check and update thank you very much
Also is it possible to provide a loop formula for the above scenario

I'm not sure I understand what you mean by "loop formula". Are you saying you still want to see how this would be done in VBA?

Here is the link where there is no reply from that forum
https://www.mrexcel.com/forum/excel-...light=Vba+loop

It seems like it should be as simple as replacing the AVERAGE() function with an AVERAGEIFS() function. Using my conditional formatting formula from post #10 as an example: =AND(F$4<>"Grand Total",F5>AVERAGEIFS($D5:E5,$D5:E5,">0")) Or am I misunderstanding something that makes it more difficult than an AVERAGEIFS() function?