Explanation
When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. So, in this case the formula =B4>100 is evaluated for each of the 40 cells in B4:G11. Because B4 is entered as a relative address, the address will be updated each time the formula is applied. The net effect is that each cell in B4:G11 will be compared to 100 and the formula will return TRUE if the value in the cell is greater than 100. When a conditional formatting rule returns TRUE, the formatting is triggered.
Using another cell as an input
Note that there is no need to hard-code the number 100 into the rule. To make a more flexible, interactive conditional formatting rule, you can use another cell like a variable in the formula. For example, if you want to use cell G2 as an input cell, you can use this formula:
=B4>$G$2
You can then change the value in cell G2 to anything you like and the conditional formatting rule will respond instantly. Just make sure you use an absolute address to keep the input cell address from changing. Another way to lock the reference is to use a named range since named ranges are automatically absolute. Just name cell G2 “input” then write the conditional formatting formula like so:
=B4>input
Explanation
When you use a formula to apply conditional formatting, the formula is evaluated for each cell in the range, relative to the active cell in the selection at the time the rule is created. So, in this case, if you apply the rule to B4:G11, with B4 as the active cell, the rule is evaluated for each of the 40 cells in B4:G11 because B4 is entered as a fully relative address. Because we are using OR with two conditions, the formula returns TRUE when either condition return TRUE, triggering the conditional formatting.
Using other cells as inputs
You don’t have to hard-code the numbers into the rule and, if the numbers will change, it’s better if you don’t.
To make a more flexible, interactive conditional formatting rule, use other cells like variables in the formula. For example, if you want to use cell E2 for the lower limit, and cell G2 for the upper limit, you can use this formula:
=AND(B4<$E$2,A1>$G$2)
You can then change the values in cells E2 and G2 to anything you like and the conditional formatting rule will respond instantly. You must use an absolute address for E2 and G2 to prevent these addresses from changing.
With named ranges
A better way to lock these references is to use a named ranges, since named ranges are automatically absolute. If you name cell E2 “lower” and the cell G2 “upper”, then you can write the conditional formatting formula like so:
=AND(B4upper)
Named ranges allow you to use a cleaner, more intuitive syntax.
