OR in conditional formatting formula brakes condition giving unexpected outcome

Question

I have a very unexpected outcome of OR used in conditional formatting formula. Take a simple sheet:

Now add conditional formatting with formula:

=B4:G7=$A$1

It works as expected, with true value for cells containing "A"

Now try another formula:

=OR(B4:G7=$A$1;FALSE)

Which should basically mean the same. Well.. the outcome is very different:

What's even more confusing is that chaging, for example, cell I7 to "a" influences the outcome:

This is exact formula I have used, with "LUB" being "OR", and "FAŁSZ" being "FALSE":

Edit: I forgot to stand the question. Question is: why does it happens? How do I use OR correctly in that context?

Answer 1

First, you need to understand how absolute and relative reference works in conditional formatting. Formula references refer to the very top left cell in the applies to range. If the formula has relative references, then for each applies to cell the cell in the formula reference shifts accordingly, if absolute - then the reference does not change. In your case, the formula =B4:G7=$A$1 is applied to cell B4, to cell C4 - =C4:H7=$A$1, to cell B5 - =B5:G8=$A$1, the same thing happens with formula =OR(B4:G7=$A$1;FALSE).

Second, the formulas =B4:G7=$A$1 and =OR(B4:G7=$A$1;FALSE) work completely differently. The first case actually compares the first cell of the range with $A$1, i.e. in the previous example =B4=$A$1, =C4=$A$1, B5=$A$1. In the second case, the range is divided into cells, they are compared with $A$1 and the results are passed to the OR function, i.e. =OR(B4:G7=$A$1;FALSE) => =OR(B4=$A$1;C4=$A$1;D4=$A$1 ... G7=$A$1;FALSE) and if even if one is TRUE, then you also get the resulting TRUE.

Consequently, we can conclude that the following formula will work correctly for you in this case:

=OR(B4=$A$1;FALSE)