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

Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
How to Create Time Duration
I have a report where I need a formula to figure out time duration. For example, employee starts at 8:00 am and ends at 9:00 am, I need the next column to tell me 1.00 to indicate he worked 1 hour. I will be using military time throughout this sheet.
Formula with range data
Hello, I'm looking to see if anyone knows a formula that would add owner income and co-owner income together to reflect in Joint Income column. I'm not sure how to do this with data in ranges. Thank you.