Forum Discussion
Color checkbox cell based off checked/unchecked box and date due in Excel
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 🙂
- 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.
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.
