We can use a single conditional format rule to highlight multiple groups and control each group via tick boxes in Google Sheets.

To explain further, column C contains groups and column B contains tick boxes. The tick boxes are only in the very first row of each group.

I am talking about the tick box that is available under the Insert menu. The same is available under the menu Data > Data validation > Criteria > Tick box with more controls like validation help text, reject input, etc.

All the groups in column C are covered within a single conditional format rule. So by toggling the tick boxes we can highlight or unhighlight individual groups.

This post describes how to highlight multiple groups with a single format rule in Google Sheets. Also controlling the highlighting via tick boxes.

Actually if you have 2-3 groups as above you can probably use three format rules (one for each group) to achieve the above output. But it’s never suggestible with a very large number of groups in a column.

Though this post is about control highlighting of multiple groups with a single rule in Google Sheets, let me start with the multiple rules first.

Multiple Rules for Each Group and Checkbox Control

I have already told you I am not recommending this method. Here are the formulas and settings for your learning purpose.

The formulas (rules) should be inserted in Format > Conditional formatting > Format rules > Custom formula is.

For the first rule, I’ll show you the screenshot, follow the instructions for other rules.

Rule # 1 Formula:

=$B$2=TRUE

Apply to range: C2

Rule # 2 Formula:

=$B$3=TRUE

Apply to range: C3:C8

Rule # 3 Formula:

=$B$9=TRUE

Apply to range: C9:C11

Rule # 4 Formula:

=$B$12=TRUE

Apply to range: C12:C16

It’s a pain to create multiple rules without typos, right?

With a single rule, we can highlight multiple groups in Google Sheets. A lengthy formula that I’m going to explain in three steps below.

Single Highlight Rule for Multiple Groups and Control Tick Boxes in Google Sheets

Here are the steps in a nutshell.

1. Fill TRUE or FALSE between Tick Boxes.
2. Return Row Numbers of TRUE Value Cells.
3. Regexmatch Row Numbers (Step # 2) to Highlight Multiple Groups.

In the third step we will have the formula (rule) to use in conditional formatting. Here are the steps in details.

Formula to Fill TRUE or FALSE in Blank Cells Between Tick Boxes (Step # 1)

Step # 1 Formula:

=ArrayFormula(if(row($B$2:$B)<=MATCH(2,1/($C:$C<>""),1),lookup(row($B$2:$B),row($B$2:$B)/if($B$2:$B<>"",TRUE,FALSE),$B$2:$B),))

For the explanation purpose I’ve inserted the above formula in cell E2.

Actually we don’t want to insert any formula within any cell. The formula will go in conditional formatting only.

Just for example, please see cell B3 on the image below.

The tick box in that cell is unchecked. That means the value in cell B3 is FALSE.

My formula above copies this value until the next tickbox, i.e. the one in cell B9. This is happening with all the tickboxes.

In concise, the above formula autofill a value until the next value that starting from cell B2 to B16. The B16 is determined by the last value in column C.

In our single conditional format rule that highlights multiple groups in Google Sheets, the above formula plays a crucial role.

To learn the formula above with details, please read this guide – Array Formula to Fill Blank Cells With the Values Above in Google Sheets.

A Twist with a Helper Column (Just for Your Information)

Actually, if you are ready to use a helper column (here column E), then no need to proceed to the further steps.

Just use the below single rule to highlight multiple groups that we can control via checkboxes (tick boxes).

=E2=TRUE

Apply to range: C2:C

This is possible because of the values in E2:E change when you interact with the tick boxes.

But I’m not in favor of using a helper column as above to highlight and unhighlight groups of rows. Then?

Please proceed to the steps # 2 and 3.

Return Row Numbers of TRUE Value Cells (Step # 2)

In most of the cases, Conditional format won’t support an array formula output to match the output values (supposed rows if you enter the formula in any column).

So we can’t match the TRUE values, which we want to highlight, without a helper column as above. Then?

Either you should use a helper column as above (column E) or convert the E2 formula output to row numbers. By following the latter, we can avoid using the helper column E.

To convert Step # 1 output to row numbers, we can follow this generic formula.

=ArrayFormula(if(step_1_formula_output=TRUE,row($B$2:$B),))

Step # 2 Formula (based on the generic formula):

=ArrayFormula(if(ArrayFormula(if(row($B$2:$B)<=MATCH(2,1/($C:$C<>""),1),lookup(row($B$2:$B),row($B$2:$B)/if($B$2:$B<>"",TRUE,FALSE),$B$2:$B),))=TRUE,row($B$2:$B),))

We are one step closer to code the formula rule to highlight multiple groups and control it via tick boxes in Google Sheets.

What we want to do is to match the ‘real’ row numbers with the row numbers returned by the above formula.

If there is a match of row number that row will be highlighted. We can use the Regexmatch function for this.

Regexmatch Row Numbers to Highlight Multiple Groups (Step # 3)

Let me start this third step with the syntax of the REGEXMATCH function.

REGEXMATCH(text, regular_expression)

text (first argument):

row(B2)&""

We want to match whether row # 2 has a match in step # 2 formula output as our data starts from row # 2.

The conditional format will automatically do the match in the next rows if the cell reference is a relative reference, e.g. row(B2)&"", not an absolute reference, e.g. row($B$2)&"".

Note: The &"" formats the row number to text as the function Regexmatch only takes text values.

Let’s convert the step # 2 output as a ‘regular_expression’ (second argument).

Generic Formula:

="^"&textjoin("$|^",true,step_2_formula&"$"

Note: The TEXTJOIN joins the row numbers and inserts a group of signs, i.e. $|^, between the row numbers.

regular_expression (second argument):

="^"&textjoin("$|^",true,ArrayFormula(if(ArrayFormula(if(row($B$2:$B)<=MATCH(2,1/($C:$C<>""),1),lookup(row($B$2:$B),row($B$2:$B)/if($B$2:$B<>"",TRUE,FALSE),$B$2:$B),))=TRUE,row($B$2:$B),)))&"$"

Result (just for your reference):

^2$|^12$|^13$|^14$|^15$|^16$

Here is that single formula rule to highlight multiple groups that can control via checkboxes in Google Sheets.

=regexmatch(row(B2)&"","^"&textjoin("$|^",true,ArrayFormula(if(ArrayFormula(if(row($B$2:$B)<=MATCH(2,1/($C:$C<>""),1),lookup(row($B$2:$B),row($B$2:$B)/if($B$2:$B<>"",TRUE,FALSE),$B$2:$B),))=TRUE,row($B$2:$B),)))&"$")

Apply to range: C2:C

Sample Sheet 22520

Additional Reading

• Highlight a Set of Alternate Rows in Google Sheets.
• How to Highlight Every Nth Row or Column in Google Sheets.
• How to Conditional Format a Chessboard in Google Sheets.
• Google Sheets: Highlight Rows When Value Changes in Any Column.