Forum Discussion
Color checkbox cell based off checked/unchecked box and date due in Excel
mtarler So I will not do all the work 🙂 but will try to give you enough to see what to do.
As i noted above first assign a cell for each checkbox (this will be annoying and I think if you did it first and then copied the checkbox it may have auto incremented and made it easier). So right click and choose Format Control... then under Cell Link select the cell under the box.
> > > >
now when you click the box the cell will reflect the value of the box:
As I mentioned you can easily change the text color to match the background to hide it.
Now you have actual cell values you can use and reference w/r to the state of the checkboxes.
Next I insert a ton of columns before each date column. This technically isn't necessary as you could use a bunch of different conditional formatting formulas or write a more complicated formula but this keeps it 'easy' so I can make 1 pretty easy formula for OVERDUE, 1 for ALMOST DUE, and 1 for DONE
NOTE that I set BOTH the format for the fill color AND the text color so that TRUE/FALSE stays not visible
So in the above I show the 3 rules and you can see those extra columns I inserted and how the first row shows an example of FALSE (text not set to background), then RED because overdue, GREEN because checkbox done and YELLOW because almost Due. NOTE those are the only 4 checkboxes I formatted the controls on. In the attached I went ahead and hid some of the columns too.
mtarler I really appreciate the help. My problem is the understanding of the syntax that goes into creating the formula. So you showed me that you could do it but I don't know the syntax to execute that formula i.e., I have no idea what MAX(B2:K2) range is referring to. What data is in the extra columns that you are inserting. I checked and there is nothing so I don't know why the columns need to be there if there is no data in them. I am trying to add the range to the formula to all the checkboxes and I am getting this error.
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
