Conditional Formatting if Checkbox is Checked

mavalos

Hello everyone,

I created a checkbox column with 3 cells set to toggle on and off conditional formatting rules.

My idea is to paint certain cells if certain conditions are met AND one of the 3 cells is checked. 

I'm doing this mainly for aesthetic purposes as I don't want to have my sheet multicolored all of the time. Using the checkboxes allows for quick access to turn the formatting on or off.

I played around with the conditional formatting menu but can't seem to find a way to detect if a specific cell is checked.

Can what I'm trying to achieve be solved this or some other way?

Thanks!

Andrée Starå

Hi,

Do you want it to be by row or something else?

Can you describe your process in more detail?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

mavalos

Thank you for your reply Andrée. 

Basically I'm using the Gantt template and want to show delayed tasks. I have a column that calculates delay from the baseline start dates with the actual start dates so I want to paint red the cells that have a time delay. 

I can do this with conditional formatting but just wanted to enable that toggle functionality right in the sheet. 

Thanks!

Mike Wilday

Basically, in conditional formatting, you would state the condition would be if the checkbox column is  Checked then color the cells or row. 

Andrée Starå

Happy to help!

I saw that Mike answered already!

Let me know if I can help with anything else!

Best,

Andrée

Paul Newcome

Reading through this a few times...

 

If you have not already, you will need to create another helper column that I would suggest is a text/number column.

 

In this column you would enter a formula that would basically produce a (for example) letter based on which of the three boxes are checked. Entering this into all rows would have that letter (or letters if more than one of the 3 boxes are checked) populated. You would then base you conditional formatting off of this new row.

 

Something along the lines of

 

=IF([Checkbox Column]$1 = 1, "a") + IF([Checkbox Column]$2 = 1, "b") + IF([Checkbox Column]$3 = 1, "c")

.

This will populate "a" is the first box is checked, "b" if the second, "c" if the third or a combination of the letters if multiple boxes are checked.

 

Your conditional formatting would then be based off of this text column so that if it CONTAINS "a", then this format. Another rule for "b", and another for "c".

.

Depending on your needs, this will allow multiple formats in different columns across the same row if multiple boxes are checked looking at different things.

mavalos

Thank you Paul! This is exactly what I was looking for

mavalos

Thank you for your reply Mike.

Yes this is what I was going for at the beginning but wanted to have one general checkbox to turn on and off the formatting instead of one per row. 

I appreciate your help!

Paul Newcome

Happy to help!