In a Google Sheet with a cell range of 26x26 (so A1:Z26), I need to conditionally format (change the color to green) a rectangle area that is defined by user input.

Example of user input (4 values required):

hsize = 5 / vsize = 4 / starth = 3 / startv = 2

This means that the conditionally formatted area should be a rectangle from C2:G5 because the start cell horizontally is 3 (column C) and vertically 2 (row 2), and the size of the rectangle horizontally is 5 (C,D,E,F,G) and vertically 4 (2,3,4,5).

I already solved this with Apps Script but due to given restrictions I have to implement this without using any scripts.

I have numbered the whole 26x26 area (=sequence(26,26)) to get numbers from 1 to 676 that I could then use for the conditional formatting.

By doing this, I can limit the conditional formatting to the values between the start and the end value (in the example above that would be 29 (C2) and 111 (G5)). This works by using a simple and/if formula in the conditional formatting.

But the problem with this is that all the cells with values from 29 to 111 are now colored, not only the rectangle C2:G5.

I can't figure out how to define a formula that does what I need. How can I do this and limit the highlighted area to the defined cell range of the rectangle?

[Picture here]: green is the conditional formatting from 29 (C2) to 111 (G5), but what I actually need is that only the red-framed area should be shown in green.

CodePudding user response：

try:

=REGEXMATCH(""&A1, "^"&TEXTJOIN("$|^", 1, INDIRECT(
 ADDRESS($AB$4, $AB$3)&":"&ADDRESS($AB$2 $AB$4-1, $AB$1 $AB$3-1)))&"$")

or better:

=(COLUMN(A1)>=$AB$3)     *(ROW(A1)>=$AB$4)* 
 (COLUMN(A1)<$AB$1 $AB$3)*(ROW(A1)<$AB$2 $AB$4)