conditional formatting

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Conditional Formatting

Conditional Formatting

resolvedResolved · Low Priority · Version 2003

Priya has attended:
Excel Intermediate course

Conditional Formatting

How do I make cells turn a certain colour when an expiry date has been reached eg all cells to turn red on 1 July

Edited on Thu 17 Jul 2008, 08:25

RE: Conditional Formatting

Hi Priva,

Thank you for your post, welcome to the forum, in reply to your question:

First highlight the range of cells that you want to apply the formatting to. For example, select all of column A since we don't know how many rows will have expiration date values.
Next, under the Format menu, select Conditional Formatting.
When the Conditional Formatting window appears, select "Formula Is" in the drop down. Then enter the following formula:
=ISBLANK(A1)=TRUE
What this formula means is that if any cells in column A are blank, do not apply any formatting. The value of A1 is put in the IsBlank function since this is the first value in the range of cells that you've selected. Since the formula uses relative referencing each value in column A will be evaluated individually.
Next, click on the Add button to create a second condition.
In Condition 2, select "Cell Value Is" in the first drop down and "less than"/


 

Excel tip:

Entering text in Multiple Worksheets

If you have a number of worksheets in a workbook that require the same information (data or tables) on each worksheet, this can be done as follows:

Hold down the Ctrl key and click one or more of the additional worksheet tabs (i.e. Sheet2, Sheet3, etc).

In your mainsheet (Sheet1) enter the required data or design a table.

When done 'Click' on the other sheet tabs and you will see that the information entered in Sheet1 is on all the other selected worksheets.

NB Do not forget to deselect the worksheets - otherwise you may add data to the main worksheet and all the selected worksheets will also have that data!

View all Excel hints and tips


Server loaded in 0.12 secs.