# advanced accumulative conditional formatting based on tick boxes

1. ## advanced accumulative conditional formatting based on tick boxes

I have attached a sheet explaining what I need as its easier to look at than describe.

thanks.

2. ## Re: advanced accumulative conditional formatting based on tick boxes

Use formula based conditional formatting off the linked cells to your check boxes, or even work the formula in to a cell and base off that.

3. ## Re: advanced accumulative conditional formatting based on tick boxes

I've tried.

I can normally do all that with ease. but what im having trouble with it telling the sheet WHAT cells to colour. because it needs to be relative to the previous colour bar.

e.g I cant tell it to code certain cells in the conditions or code because the first cells it needs to colour depends on the previous LAST coloured cell

what im trying to describe and failing to is..

say tickbox one is ticked on 1/5/2014 its easy to get that box filled red.

but if tickbox 2 is ticked on the 4/5/2014, how do I tell it to fill the boxes from 2-4 in green. AND make it intelligent enough to figure that out itself because tick box 1 could be ticked on any day

4. ## Re: advanced accumulative conditional formatting based on tick boxes

FORUM_D8.xlsmDoes this help? I would have some hidden rows that work out the logic and do the cond formatting off them, basically build off my formula and have a hidden "helper row" underneath to thin out what goes in the cond formatting formula.

5. ## Re: advanced accumulative conditional formatting based on tick boxes

Just FYI for anyone interested, here is a solution that exploits the order of rendering the CF and thus eliminates the need for helper formulae.
I only needed to add a little table to store the day of month value of the checked dates, next to the tick box TRUR/FALSE cells.
Because the colours are sequential (left to right), you can arrange the CF so that the first format (RED) is the last rule and the last one (Amber) is the first... (turns out Bob Dylan was right after all)
If you look in the CF of the attachment, you will see the rules in the following order...

6. Amber =AND(R16C3<>"",R4C<=R16C3,R4C>R15C3)
5. Pink? =AND(R15C3<>"",R4C<=R15C3,R4C>R14C3)
4. Yellow =AND(R14C3<>"",R4C<=R14C3,R4C>R13C3)
3. Blue =AND(R13C3<>"",R4C<=R13C3,R4C>R12C3)
2. GREEN =AND(R12C3<>"",R4C<=R12C3,R4C>R11C3)
1. RED =AND(R11C3<>"",R4C<=R11C3)

This takes care of the rendering and gives the desired effect without having to do any other calcs.

The attachment has a switch driven from cell A1 to drop it into test mode and just loads test dates when the box is checked.
Just release it to Run mode from the drop down in A1 to activate the original functions.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1