dimanche 15 septembre 2019

Conditionally format cells with matching text if a checkbox is checked in Google Sheets

How can I apply conditional formatting with a custom formula that uses text AND a checkbox as its criteria?

I have two columns of names and I want to be able to toggle highlighting with checkboxes from a third column. So if I check the "Paul" box, all the cells with "Paul" in it will highlight.

Desired result: Example of success

I can achieve the proper highlights without the checkbox if I use the following formula in the Custom Formula box of Conditional Formatting: =countif(B2,"Paul")

But I only want to activate this code if D3=TRUE

I tried =IF(D3=TRUE,countif(B2:C11,"Paul")) Which is a formula that works in a cell (returns the number 8)... but this only seems to highlight one cell (B2) when entered in the Custom Formula box.

I've seen examples of checkboxes toggling formatting on cells or rows, which is where I got the COUNTIF code, but nothing I've seen helps with the specific text search.

I'm happy to set up a different formatting rule for each name, unless there was a HLOOKUP formula that worked dynamically!




Aucun commentaire:

Enregistrer un commentaire