Forum Discussion
How to have excel recongnize a conditional format and adapt it to each cell
I am creating a simple weight tracker. The current setup has it where you can enter your starting weight in Cell A2, Targeted Loss in cell B2 and based on 5 days a week it will total your needed dail...
I added it to your sheet:
=B5>($A$2-$D$2*(5*(ROW(B5)-ROW(B$5))+MAX(0,MIN(5,COLUMN(B5)-COLUMN($B5)))))
Basically define the range for the conditional formatting and then do the formula based on the most top left cell (i.e. B5) and use the $ to fix the column or row that you do NOT want to move as the formula is applied to each successive cell. The MAX(MIN( is to create that 'weekend off' but actually probably don't need the MAX since it won't ever go <0.
=B5>($A$2-$D$2*(5*(ROW(B5)-ROW(B$5))+MAX(0,MIN(5,COLUMN(B5)-COLUMN($B5)))))
Basically define the range for the conditional formatting and then do the formula based on the most top left cell (i.e. B5) and use the $ to fix the column or row that you do NOT want to move as the formula is applied to each successive cell. The MAX(MIN( is to create that 'weekend off' but actually probably don't need the MAX since it won't ever go <0.
I got bored and ended up formatting each cell individually but new this could be done, and couldn't wrap my head around it. Thank you for this
As a comment, keep as many rules as many different formatting you have. If rules more than different formats most probably you may improve your setup.
