Conditional Formatting Based on Single Cell Across Columns and Rows-CodePudding

I'm trying to find a single formula for conditional formatting based on a single cells value next to a section of cells. This would include both rows and columns, as well as sections next and below each section. This section would check to see if the single cell contains a particular name, which would be corresponded to a particular color.

In my example sheet, I have single cells containing a name (B2, H2, B7, H7), which corresponds to the ranges next to the cell (B2 -> D3:F5, H2 -> J3:L5, B7 -> D8:F10, H7 -> J8:L10). I'm looking for single formula that would apply to all ranges checking for a name, for example =$B$2="John" would be the color RED in each of the cell sections (D3:F5, J3:L5, D8:F10, J8:L10).

Is it possible to have a single formula for both sections horizontally AND vertically, or can it only be horizontally OR vertically? The names in B2, H2, B7, H7 can interchange, so I'm trying to make the colored regions dynamic.

Test Sheet:

Please let me know if that makes sense? Thanks!

CodePudding user response：

In order to have your conditional formula working on a grup of cells, you have to build an algorithm to reference them. Then you use custom formula in conditional formatting.

First - define a reference: I use ADDRESS formula to determine source cell from which information about formatting will be taken.

=address(5*int((row()-1)/5) 2,6*int((column()-2)/6) 2,4)

It means that for each row smaller than seven display 2 then display 7th and so on (works for bigger tables) And for each colmumn index smaller than eighy display 2 then display 8th, etc. Number 4 here determines only notation (A1 notation).

When I am sure that all the references are correct I can empty cells and leave space for real content.

While you have this reference, you can make 4 different Conditional formats. Each should work for all ranges. So in field "Apply to range" you write them all:

D3:F5,J3:L5,D8:F10,J8:L10

Then you choose Custom Formula to format cells. It goes:

=Indirect(address(5int((row()-1)/5) 2,6int((column()-2)/6) 2,4))="John"

This means that for each cell on which formatting is applied it will check wheter corresponding cell defined by algorithm meets the condition. Here it is "John".

Then you copy this rule for all the names and define color for each name.

My solution file is available here: https://docs.google.com/spreadsheets/d/1z2bdeEBpYJX16xZk6apgfPgMGerCReaXCKq2Ay1RZ10/copy