LinkBack URL
About LinkBacks
I have attached a sheet explaining what I need as its easier to look at than describe.
thanks.
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.
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.
Hope this helps
Sometimes its best to start at the beginning and learn VBA & Excel.
Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
Available for remote consultancy work PM me
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
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.
Last edited by nathansav; 05-14-2014 at 06:59 AM.
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.
Last edited by coolblue; 05-19-2014 at 03:45 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks