How to highlight current row and next row if checkbox is true with conditional formatting (Google Sheets)

Question

Is there a way to apply conditional formatting to an entire Google Sheet so that for every checkbox marked true, it highlights the entire row and the next row?

My sheet has a checkbox in every other row in column A (i.e. A2, A4, etc.), and when it is checked (TRUE), I want to highlight the current row and the next row.

Example:

If the A2 checkbox is checked (TRUE), I want $A2 (A2:E2) and $A3 (A3:E3) to highlight the same color. IF A4 is true, I want $A4 (A4:E4) and $A5 (A5:E5) to highlight.

I know I can do this for each range (i.e., range=A2:E3, custom formula =$A$2=TRUE), but that would take forever and I have a LOT of checkboxes.

Answer 1

There many be many ways of solving this question. The following should be considered as just one option.

You have a check box in cell A2 and every other row of column A. When a checkbox is checked, you want to highlight the cells in that row and following row from Column A to Column E. You have a lot of checkboxes and want a fast method of creating the conditional formatting rule.

This answer consists of three steps:
1 = Build a Conditional formatting rule for row #2
2 = Build a Conditional formatting rule for row #3
3 = Apply the Conditional Formatting rules to the rest of the checkboxes in Column A.

---

Build a Conditional formatting rule for row #2

• For the Range=A2:E2, create a conditional formatting rule, using a custom formula.
• the custom formula is =$A2=true

Build a Conditional formatting rule for row #3

• For the Range=A3:E3, create a conditional formatting rule, using a custom formula.
• the custom formula is =$A2=true. This is NOT a misprint - this is the same formula as for row 2.

Apply the Conditional Formatting rules to the rest of the checkboxes in Column A.

• use the cursor to select/highlight the range "A2:E3"
• From the menu, select Edit > Copy.
• use the cursor to select/highlight the range "A4:E??" - where "??" is the row following the last checkbox
• From the menu, select Edit > Paste special > Paste conditional formatting only.

Comments:

• The formula is absolute for column A, but not the row number. This ensure that when the check box is ticked, the columns B, C, D and E will be highlighted.
• The formula for row #3 (and subsequent odd rows) tests whether the checkbox in the preceding row has been checked. This gives the cells for the "following" row their own criterion and ensures that the cells Columns A through E of that row will be highlighted.
• By using Paste conditional formatting only, the rules are applied to all the subsequent checkboxes and following rows without disturbing the content on cells in that range.