Color checkbox cell based off checked/unchecked box and date due in Excel

[xhaven80]

I want the cell with the checkbox to be colored based on checked or unchecked and based off of the date that it corresponds to i.e., G2 is unchecked and F2 is a past date so G2:G6 and H2:H6 should all be red. If the date is within one week of being due and the boxes aren't checked I want the checkbox cells to be yellow i.e., J4:J6 and K4:K6 should all be yellow. If the checkboxes are completed for a section the checkbox cells should be green i.e., C4:C5, D4:D5, and E4:E5 should be green.

I have scoured the web and this is rather difficult to find something to exactly what I need.

[#2]

The last I checked, checkboxes are still not embedded into cells but rather 'float' over the sheet. Furthermore, their value/status is not accessible from worksheet functions directly but instead you need to tie them to particular cells (unless you want to use VBA/macros). So I would link each of those checkboxes to the cells they correspond to/hover over. Depending on the type (Form Control vs ActiveX) you may do that by right clicking on it and selecting Format Control or you may need to go into the VBA editor and go into properties. Once that is done you can make the text in those cells match the background (i.e. not visible). Finally you can then create 3 Conditional Formatting Rules based on the value in the cell and the corresponding date. To make your life easier, I would recommend you insert a few 'dummy' columns so that every group has the same number of columns and you have that number of columns to the left of the 1 column of dates then you can use a formula like this for the past due formatting rule:
=F2*(Max(A2:E2)>Today())
where F2 is the top left of the 'Applied To' range and the upper left checkbox

If that all makes sense to you then I'm sure you can follow through with the other rules.  If not, can you supply a sample sheet (no private info) and I can show you an example of what I'm talking about 🙂

[#3]

@mtarler it will not let me attach a .xlsx sheet. How do I get you a sample?

[#4]

you can share it using OneDrive, SharePoint, or similar cloud storage. Alternately you can PM it me here by clicking my name and going to the messages section.

[#5]

@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.

[#6]

@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.

[#7]

@mtarler Duplicating your rules and adjusting for the new row it doesn't work. Also in your example, =$L$2:$BD$35 this does not work for any other checkboxes in row 2 i.e., N2,O2, AU2 etc...Why? Sorry for all the questions. They don't exactly teach this in college or at work.

[#8]

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

[#9]

@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? 

[#10]

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.