Macro: Highlight 3 or more consecutive +ve or -ve cells

[Former Member]

Dear Experts,

If 3 OR MORE consecutive cells in row are negative or positive, I need to highlight those cells in red / green resp.

How do I do this given that 4 or 5 or 6 or more cells can also be consecutively positive or negative in say 36 periods under evaluation.

Please help with the Macro syntax. I can't seem to think of an easy way out.

Thanks

Lok

[Former Member · Accepted solution]

Hi Lok

I can give the logic and I am sure you can put then into macro.

In case the cell is -ve, I will put 1 into some aux. KF and if cell is +ve will put 2 into same aux KF.

Then I will sum 3 consecutive cells in row and if sum is coming as 3 then change the color as Red and if sum is coming as 6 then change color as Green.

I hope this logic will help you with the above requirement.

Thanks

Amol

[Former Member · Accepted solution]

Thanks Amol,

I did something even more simpler for the 'running' periods. latest 3 concluding periods to last period and all is good with a basic < or > 0 condition but the trouble is now to make this Macro work for historical periods.. say 36 periods ago

Period 1 -ve

p2  -ve

P3 -ve

p4 -ve

p5 +ve

p6 +ve

p7 +ve

p9 -ve

..

..

..

..

P22 -ve

p23 -ve

p24 -ve

p25 -ve

p26 -ve

So here I need to make p1,to p4 and p22 to 26 red. and p5 to p7 green. This bit needs to happen in the background for later evaluation in conjunction with other things.. so I need to write a Macro.

How do I go about this?

Thanks

Lok