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

2
votes

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:

enter image description here

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!

1
checkboxgoogle-sheetsgoogle-sheets-formulacountifgs-conditional-formatting

1 Answers

0
votes

what you need is to lock down the D3 like this:

=COUNTIF(B2,"Paul")*($D$3=TRUE)

0

or you can use COUNTIFS like:

=COUNTIFS(B2,"Paul",$D$3,TRUE)

0

or use just this:

=(B2="Paul")*($D$3=TRUE)

0

or even like this:

=IF(($D$3=TRUE)*(COUNTIF(B2,"Paul")),1)