OzGrid

Conditional Formatting

< Back to Search results

 Category: [Excel]  Demo Available 

Conditional Formatting

 

Using Excel Conditional Formatting

Got any Excel/VBA Questions? Free Excel Help

Conditional Formatting.

In Excel 97, Microsoft introduced a nifty new feature called Conditional Formatting. It can be found via the Worksheet Menu Bar under "Format". It allows us to format a cell, or range of cells, based on a specified criteria for the cell(s), or other cell(s).

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;

Conditional Formatting Dialog

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.

 

TIP! If you need to reference another Worksheet when using Formula is you can do so in 2 ways.

  1. Name the range, Then Use The Name Apposed To The Range Address.
  2. Enclose The Range Is The INDIRECT Function. E.G INDIRECT("'Sheet2'!A1:A100")

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

See also:

Compare Two Excel Ranges
Comparing the Same Workbook
Complex Intersections Chart-Mark Points of Intersection Between 2 Lines

 

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


Gallery



stars (0 Reviews)