CODEWITHSUNDEEP
excelformatconditional-statements
Streching my competence
Streching my competence

Reputation: 381

Conditional format only applies to first cell

I have a grid of 5x5 boxes. These are checked against a mirror grid of 5x5 boxes, with values of TRUE or FALSE. In the first grid, I have checkboxes in the first grid, that results in TRUE or FALSE in the second grid and when one row is checked, a conditional format checks if H2-L2 is all TRUE and then it applies the conditional formatting to B2-F2 (the grid with the checkboxes).

The problem is, that when I try to do this for the columns, say B2-B6, only B2 changes formatting. I try to apply it to only B3 for example, and it does not work. The rule is at the top of the list and should be priorotized, of course.

The code for the first row for example is:

"Format values where this formula is true:"

=AND($H2=TRUE;$I2=TRUE;$J2=TRUE;$K2=TRUE;$L2=TRUE)

And it applies to the range:

=$B$2:$F$2

That works. However, this does only work on B2:

"Format values where this formula is true:"

=AND($H2=TRUE;$H3=TRUE;$H4=TRUE;$H5=TRUE;$H6=TRUE)

and the range is..

=$B$2:$B$6

So since B2 gets re-formatted when I check the checkboxes in B2-B6, I guess the formula is not the issue, but the range is. Is this a bug, and is it impossible to fix, or am I overlooking something?

Upvotes: 0

Views: 895

Answers (1)

Dirk Reichel
Dirk Reichel

Reputation: 7979

To go for rows/columns a bit easier, simply use:

range for both conditions: =$B$2:$F$6
formula for columns:       =AND(H$2:H$6)
formula for rows:          =AND($H2:$L2)

Upvotes: 1

Related Questions