Checkbox if checked change color of multiple adjacent cells

Copper Contributor

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!!!!

6 Replies
If the checkboxes are linked to cells, you can use a conditional format to color cells based on the value of that linked cell.

@Jan Karel Pieterse 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.

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

I cannot do VB personally but I can follow instructions/copy and paste code.
The other issue is that my employer is migrating to "dumb terminals" if you will and utilizing Office 365 for the web. Which as I recall lacks the developer tab/ribbon
Check whether your Excel on-line has the Checkbox option on the Insert tab. Your current checkboxes will not work on Excel online anyway. Perhaps better to replace them all with Data Validation, List option and then enter "Y","N" as the list.