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

**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)*

**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)*

**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.

## No comments:

## Post a Comment