Forum Discussion
Color checkbox cell based off checked/unchecked box and date due in Excel
so in theory you don't have to change those formulas or worry about the columns, you just need to assign each of those checkboxes to their corresponding cells and change the font color on those cells. The reason I added the columns was to make life easy. The formula MAX(B2:K2) is looking at the 10 columns before L2 (the 1st upper left checkbox in the applied to range) and finding the largest value, which is the date because all the others are blank. The way conditional formatting works is that you write the formula based on the upper left corner and then as excel moves to the next cell it adjusts the formula accordingly to the new location and applies it (i.e. if it moves 1 column to the right all references that aren't locked with a $ before it will also move 1 column right). So using this MAX( 10 columns ) means when you get to that section on the right that has 10 columns the 1st column will look as 9 other columns and then the date and the 10th column will look at the date and 9 other columns after. Since all those columns are either the date or blank or a TRUE/FALSE value the MAX will always be the date. If I didn't insert those extra columns then 'looking back' 10 columns may compare multiple date from different sections. In this way i could write 1 'overdue' formula for the whole sheet. Alternatively you could write a separate 'overdue' function for each group and use $B2 as the reference to the date (assuming that is where it is) for that section and then the next section might use $F2 and so on.
As for the error you are getting, I have no idea what you are doing. That field is looking for a conditional formula that returns a T/F value not a range of cells. The APPLIED TO range is in the window 'above' that. You can see that in my post above in the last image with the red, yellow and green shown and to the right of those colors are the APPLIED TO ranges which in my case was the entire range.
EDIT: it doesn't work for any other checkboxes on row 2 or any other row because I only did that first step I explained "Format Control" on those first 4 checkboxes
@mtarler "in theory you don't have to change those formulas or worry about the columns, you just need to assign each of those checkboxes to their corresponding cells and change the font color on those cells."
I formatted the checkboxes in my sheet that didn't have your formulas. I got you. I noticed that the "FALSE" appears but not the true. Where in your syntax does that reference that to be invisible?
- To make the "FALSE" "invisible" you need to highlight those cells and change the TEXT COLOR to match the present background color.
When you click on a checkbox it will turn that FALSE to be TRUE. I suspect you don't see that TRUE in my file because the conditional formatting rules I set up change the background color AND the text color.