Forum Discussion

xhaven80's avatar
xhaven80
Copper Contributor
Aug 22, 2023

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

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.

  • mtarler's avatar
    mtarler
    Silver Contributor

    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 🙂

    • xhaven80's avatar
      xhaven80
      Copper Contributor

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

      • mtarler's avatar
        mtarler
        Silver Contributor
        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.

Resources