Conditional Formatting 'Blocks' of cells

Question

I am wondering if it's possible to use a custom formula in conditional formatting to highlight a single 3x3 block of cells - rather than a single cell, an entire row/column, or the entire range - based on values within the cell.

I've fussed with it a bit but, as far as I've found, the only way I would be able to get it to work would be to create multiple rules for each different cell in the block. I can't imagine how much work that would be with the number of variations I might need. So I'm hoping there's a somewhat simpler solution that I'm missing.

Here's a link to the chessboard sheet

The formatting of the cells is just to keep it similar to my intended use in case that somehow acts as a variable in any potential solution.

Is this possible? Am I missing something or looking at it the wrong way?

I've been searching all day and I can't find any examples of anyone trying to do what I'm doing here... Go figure

Edit:Thank you to Eric for the workable solution to what it looks like I was asking, however my real intent is for the cells to change based on user input in the color and checkbox cells:

WHITE + ☐ = White; BLACK+ ☐ = Black; WHITE + ☑ = Yellow; BLACK+ ☑ = Purple

the rules should not be based on the rows or columns containing the cells.

I've edited the sheet to clarify this better. I've also added my own rules that demonstrate my intent but only work on the upper left cell of each block instead of the whole block. I'm trying to discover if there's something I can do to those rules to have them affect the entire block. I've added another block of text to the sheet to explain the pitfalls I've encountered. It seems this may not be possible.

Answer 1

Yes, this can be done with four custom CF rules, each applied to range A1:X24 (the range of the "chess board":

Rule 1 (white):

=AND(ISEVEN(ROUNDDOWN((ROW(A1)-1)/3)),ISEVEN(ROUNDDOWN((COLUMN(A1)-1)/3)))

Rule 2 (black):

=AND(ISEVEN(ROUNDDOWN((ROW(A1)-1)/3)),ISODD(ROUNDDOWN((COLUMN(A1)-1)/3)))

Rule 3 (purple):

=AND(ISODD(ROUNDDOWN((ROW(A1)-1)/3)),ISEVEN(ROUNDDOWN((COLUMN(A1)-1)/3)))

Rule 4 (yellow):

=AND(ISODD(ROUNDDOWN((ROW(A1)-1)/3)),ISODD(ROUNDDOWN((COLUMN(A1)-1)/3)))

The order of the rules in the list is unimportant, since there is no overlap in conditions.

The Logic:

If the chess board grid were 8 x 8 single cells, the four divisions would be as follows:

Odd Row Odd Column

Odd Row Even Column

Even Row Odd Column

Even Row Even Column

Since you've got three rows and columns, we just get back to basics by using ROUNDDOWN( /3). In other words Rows 1, 2 and 3 all reduce to being 1; likewise, Columns 1, 2 and 3 all reduce to being 1. So any of those would reduce to 1 x 1, etc.

Since I used ROUNDDOWN, the first block actually equates to 0 x 0 (i.e., EVEN x EVEN), and so forth from there. I could have used ROUNDUP, which would have caused the first block to be 1 x 1, hence ODD x ODD. It's really irrelevant. I just chose to start from a zero count because it makes most sense in terms of computer arrays.

---

Update: (after further clarification of the goal)

What you want done is quite a bit more complex. Typically, I would say it goes beyond the type of help that can be obtained via a free forum like this, due to the custom, time-intensive, expert-required nature of working through such logic and setting up such formulas. In this rare instance, I'm happy to break my own "rule" and provide the formulas. See the newly added sheet "Erik Help 2" and the four new custom CF rules there.

Here are the rules:

white (unchecked):

=AND( OR( AND( ISEVEN(ROUNDDOWN((ROW(A1)-1)/3)), ISEVEN(ROUNDDOWN((COLUMN(A1)-1)/3))), AND( ISODD(ROUNDDOWN((ROW(A1)-1)/3)), ISODD(ROUNDDOWN((COLUMN(A1)-1)/3)))), INDIRECT(ADDRESS(MROUND(ROW(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3), MROUND(COLUMN(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3)))=FALSE)

white (checked, i.e., yellow):

=AND( OR( AND( ISEVEN(ROUNDDOWN((ROW(A1)-1)/3)), ISEVEN(ROUNDDOWN((COLUMN(A1)-1)/3))), AND( ISODD(ROUNDDOWN((ROW(A1)-1)/3)), ISODD(ROUNDDOWN((COLUMN(A1)-1)/3)))), INDIRECT(ADDRESS(MROUND(ROW(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3), MROUND(COLUMN(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3)))=TRUE)

black (unchecked):

=AND( OR( AND( ISEVEN(ROUNDDOWN((ROW(A1)-1)/3)), ISODD(ROUNDDOWN((COLUMN(A1)-1)/3))), AND( ISODD(ROUNDDOWN((ROW(A1)-1)/3)), ISEVEN(ROUNDDOWN((COLUMN(A1)-1)/3)))), INDIRECT(ADDRESS(MROUND(ROW(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3), MROUND(COLUMN(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3)))=FALSE)

black (checked, i.e., purple):

=AND( OR( AND( ISEVEN(ROUNDDOWN((ROW(A1)-1)/3)), ISODD(ROUNDDOWN((COLUMN(A1)-1)/3))), AND( ISODD(ROUNDDOWN((ROW(A1)-1)/3)), ISEVEN(ROUNDDOWN((COLUMN(A1)-1)/3)))), INDIRECT(ADDRESS(MROUND(ROW(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3), MROUND(COLUMN(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3)))=TRUE)

I will not explain the full workings of these formulas here. However, I will point out that they follow similar logic to the previous four formulas.

Directing your eye, this part is the same in all formulas:

INDIRECT(ADDRESS(MROUND(ROW(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3), MROUND(COLUMN(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3)))=

That is the part that "finds" where the checkbox is in each 3x3 block. And after the final =, you'll see either TRUE or FALSE (i.e., checked or unchecked)

The top part of each of the four formulas simply combines each combination of possibilities from the previous four formulas (i.e., the white rule with the yellow rule; and the black rule with the purple rule), paired with either a FALSE or TRUE for checked or unchecked.

Here is a summary of the logical pieces in more readable fashion than the order they appear in the formula, for the sake of making it easier to understand:

ISEVEN (AND) ISEVEN ... (OR) ... ISODD (AND) ISODD: (checked? =) FALSE

ISEVEN (AND) ISEVEN ... (OR) ... ISODD (AND) ISODD: (checked? =) TRUE

ISEVEN (AND) ISODD ... (OR) ... ISODD (AND) ISEVEN: (checked? =) FALSE

ISEVEN (AND) ISODD ... (OR) ... ISODD (AND) ISEVEN: (checked? =) TRUE

---

Round 3

I'll take one more swing at this, though it does seem that the goal is actually getting more muddy and amorphous rather than becoming more focused. And this no longer represents anything like a "chessboard" (which has a set/predictable repeating pattern) other than that a chessboard and your sheet both fall under a broad umbrella as "grids."

See the newly added "Erik Help 3" sheet.

You now have three colors introduced as options: WHITE, BLACK, GREEN. And each of those will have a checked or unchecked state. So the minimum number of rules is 3 colors x 2 states = 6 rules. (If you will have 8 colors, your minimum number of rules will be 16, etc.)

The rule is essentially the same for every state (here, I will show it for WHITE/unchecked):

=AND( INDIRECT(ADDRESS(MROUND(ROW(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3), MROUND(COLUMN(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3)))=FALSE, INDIRECT(ADDRESS(MROUND(ROW(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3), MROUND(COLUMN(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3)-1))="WHITE")

The only parts that will change are the final color word in quotes, and the FALSE midway through (which will be either FALSE for unchecked or TRUE for checked).

As such, you can copy that rule somewhere, make those two changes as you go for each color/state, and then copy and paste it into the rule field.

If this somehow all works as you had hoped, fantastic.

But if I've still got it wrong, I'm afraid I really do need to make the choice to bow out at this point and let someone else see if they can understand what you want better than I apparently am able to do.

Answer 2

It is possible to apply conditional formatting to a range of cells based on the conditions of one cell using the custom formula option and a logical operator function. Im on mobile so I’m not going to write the code here but I made a duplicate on your example sheet and did the first square. It looks like this:

=AND($B$3=“WHITE”, $C$3=“TRUE”) for cells A1:C3 to desired color

This would be copied to 4 conditional rules and adjust the “WHITE” or “TRUE” to black, false, etc. respectively. Now you can copy the square and then paste > conditional formatting only, to each chess square respectively. You WILL have to go through each one and update the $B$3 to $B$6 (or the corresponding cell).

There is no easy way to do this without copy and paste and updating the absolute cells. It is tedious but could probably be done in 10 minutes.