2

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]1: 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.

player0
  • 124,011
  • 12
  • 67
  • 124
v0y463r
  • 23
  • 3

1 Answers1

1

try:

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

enter image description here

or better:

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

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • It gives the right result but second formula doesn't seem right to me - surely =(COLUMN(C2)>=$AB$3) \*(ROW(C2)>=$AB$4)* (COLUMN(C2)<$AB$1+$AB$3)*(ROW(C2)<$AB$2+$AB$4) – Tom Sharpe Sep 29 '22 at 13:20
  • I believe the first one should be =REGEXMATCH(""&A1, "^"&TEXTJOIN("$|^", 1, INDIRECT(ADDRESS($AB$4, $AB$3)&":"&ADDRESS($AB$2+$AB$4-1, $AB$1+$AB$3-1)))&"$") for same reason – Tom Sharpe Sep 29 '22 at 13:32
  • 1
    Fantastic, thank you both so much! Yes, there was something wrong in both formulas (vsize or $AB$2 was not included) but that was an easy one to fix. Problem solved! – v0y463r Sep 29 '22 at 14:04