Copy conditional formatting and checkboxes

[Erlull]

Hi,

I'm making a "maintenance plan" spreadseet, see attached picture. As it is now, when you write in the "description" cell, the "completed" cell turns red, just to highlight that there is something to be done there. When it's finished, you tick off the "completed" checkbox, and that whole row turns green. This is done by conditional formatting and True/false from the checkbox.

My question is, is it an easy way to add more rows to this form? And insert rows between two cells, without f*** up the formatting?
It happened three times that I thought I was finished with the form, and then realized I need to add a row for this and that, and I ended up to redo the whole formatting every time. So after doing that 3 times, I decided to ask here if there is an easier way

Please help...

Also, I have two VBA/Macro buttons for the checkboxes "check all" and clear all" for the checkboxes. But when I turn on sheet protection, they won't work. Any suggestions?

 

[6StringJazzer]

An issue with checkboxes is that when you copy and paste them (it looks like you are using Forms checkboxes), they still link back to the same cell as the original. You have to either update it manually, or write VBA to associate each checkbox with the correct cell.

Checkboxes are protected similar to cells. If you want to allow them to be clicked in a protected sheet, you have to Unlock them in the configuration dialog for the control.

Personally I don't use checkboxes like this (i.e., repeat on every row) because of the issues you are seeing. I just put an X in the cell. It's not a gee-whiz but it's not any harder to use and easier to maintain.

 

[My Aswer Is This]

I never use a lot of checkboxes to perform some tasks.
I use something like DoubleClick on a certain cell to perform a task.
Or a cell change event script.
Enter a certain value in a cell and something happens
Like enter "Alpha" in any cell in column "G" and on the same row something happens in Column K
If something like this would work let me know here.
And give specific details.