A simple example would be to use Conditional Formatting to highlight
all cells in any given range that are greater than 100. Let's say this range is A1:A100. We would Start by selecting cells A1:A100, Starting from cell A1. This will ensure A1 is the active cell in the selection and from this, Excel will know all other cells we specify are relative to cell housing the Conditional Formatting. Now go to Format>Conditional Formatting and then choose "Cell value is", then "Greater than" and then type 100 in the far right, see below;
Now click the "Format" button and choose the desired formatting for all cells greater than 100. After this, click "Ok" then "Ok" again. The same logic can be applied to any other of the criteria we can choose. E.g "Less than", "Equal to" etc
The only drawback with this way, is that cells
housing text may be seen as having values
greater than 100! We can over-come this by resorting to the "
Formula is" option as apposed to "
Cell value is". When using the "
Formula is" option, any formula we use,
MUST evaluate to either
True, or
False. With this in mind, here is a formula that we can use
in place of simply 100. =AND(ISNUMBER(A1),A1>100)
Again, it is vital the you select Starting from
A1 as all other formulas, in
A2:A100, will change their cell references relatively. That
is, A2 Conditional Formatting will read;=AND(ISNUMBER(A2),A2>100)
If you are not already aware,
both conditions in an
AND Function must
evaluate to True for the
Function to return True. In other words, all cells in
A1:A100 will need to house a number AND the
number must be greater than 100 for the chosen formatting to be applied.