Conditional Formatting if Checkbox is Checked

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!

Find more posts tagged with

Sheets

Formulas

Conditional Formatting

Project Management

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…