Forum Discussion

JKrider's avatar
JKrider
Copper Contributor
Nov 16, 2023

Checkbox if checked change color of multiple adjacent cells

Greetings, 

Very novice user here.... I have a spreadsheet that I created for a group of individuals of varying levels of experience to use in order to keep track of fleet vehicles.  I created columns as follows: 

Column A is vehicle ID

Column B is operator/s

Column C is a checkbox indicating operational status of the vehicle

Column D is replacement vehicle ID

Column E is reason for inoperable status (if indicated by column C checkbox)

IF a checkbox in Column C is checked it fills the cells in Column C, D and E with red for that particular row visually indicating it is inoperable.

 

I used Format Control to fill the specific cells with red if the checkbox status was True for that row.  The problem this created is that when a user needs to add a new row they normally use the copy and paste method in order to keep the rows uniform.  In doing so the formatting is obviously copied to the new row and as a result, if a user creates row 4 by copying row 3 when the checkbox in C3 is checked it fills rows 3 AND 4 with red.  So the formatting has to be re-done for the new row.  These are all inexperienced users who don't normally know how to use format control let alone add a checkbox.

 

So after that long winded background, my question is: Is there a more efficient way to apply to the format control I detailed above to a spreadsheet selection?  In other words I want to apply the same formatting to rows 3 through 40 in one shot so that if the checkbox for a particular row is checked it fills columns C, D and E with red for ONLY that row.  Or do I have to apply formatting to each and every row individually like I did before?  I hope that makes sense..... and thank you in advance for the help!!!!

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    If the checkboxes are linked to cells, you can use a conditional format to color cells based on the value of that linked cell.
    • JKrider's avatar
      JKrider
      Copper Contributor

      JKPieterse they are linked to cells.  My question is whether or not there is a more efficient way to link the cells.  For each checkbox I linked the appropriate cells in order to apply a fill color the specified cells for each and every row.  It took me forever.  I'm looking for a way to select several rows and apply the formatting without having to do each one.... Or to duplicate the formula over and over again accurately.  Currently if you copy and paste the formatting it does not recognize that it's a different row and it copies it verbatim.  After doing this and checking the check box it fills in the cells in every row I pasted the formatting into.

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor

        JKrider That is the issue with those checkbox controls. If you wait a bit, Excel will have a far better option: Checkboxes you insert into the cell natively. But that is still only available in the Insider builds of Excel I'm afraid.

        Are you OK with a bit of VBA to handle this?

Resources