0

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.

I created a sample "chessboard" sheet to somewhat simplify and help illustrate what I'm trying to do:

And here are the parameters I'm trying to accomplish

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.

2 Answers 2

0

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.

11
  • Oh man, I think I wasn't clear enough in my original explanation. This does indeed cause the cells to behave the way the sheet was formatted in my original example, however what I was wanting was for the formatting for the entire to change based on user input in the color and checkbox cells. I've changed the sheet to indicate this better. Nov 4, 2021 at 5:28
  • Your goal as newly stated is not clear. And we'll want to get that straight before moving any further. Why is the entire range A1:X24 blue right now? What does that signify? According to what appear to be your rules as now stated. the unchecked "White" blocks should all be white. So why the blue? And what action would cause a block to change from the current default blue to white?
    – Erik Tyler
    Nov 4, 2021 at 12:04
  • Haven't heard back from you in 16 hours. Are you still interested in my help on this? It can still be done in four rules; I just need your feedback on my above comment.
    – Erik Tyler
    Nov 5, 2021 at 3:23
  • Erik, sorry, I got my second dose of the vaccine and it kind of knocked me down and out a bit. Feeling better now. The colors of the cells are arbitrary. I just made the cells Blue by default so that there would have to be a conditional formatting rule to make them white. Just an artifact of the chessboard idea I had to demonstrate my intent. The goal is to make the color of each group of cells change based on the color that is selected in the color cell and an additional AND rule to make them a different color if the box is checked. Nov 5, 2021 at 22:40
  • Ah, sorry to hear that. I totally understand. See my updated post and corresponding updates to your spreadsheet. If this now satisfies the goal, please do mark the post as "Best Answer." This helps the contributor community to see at a glance which issues have been fully resolved, so that more work isn't needlessly put in; and it allows future site visitors to more easily locate answers that might pertain to their own similar issues.
    – Erik Tyler
    Nov 6, 2021 at 1:00
0

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.

2
  • Kevin, this is what I was trying to avoid haha. This chessboard file is just a very simplified version of what I'm actually doing and therefore it would be MUCH bigger with a lot more more combinations. It's for my personal budget sheet and essentially what I'm trying to do is automatically color my transactions to be a lighter version of a certain color depending on the category (represented by the color box in my example), and a darker version of that color when the transaction has gone through my account (represented by checking the box) Nov 5, 2021 at 22:45
  • @Filteredgenius The manual work of this is due to the relative nature of custom conditional formatting references. If I’m interpreting your description correctly, the same formula logic could be used for the entire range without the absolute references, and therefore you would only need to make 4 rules.
    – Kevin
    Nov 5, 2021 at 22:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.