I am trying to generate the following scenario:

1. If a cell is blank, colour blue
2. If a cell whose inserted date is less than or equal to the date in
another cell, colour green
3. If a cell whose inserted date is greater than the date in another cell,
colour orange.
4. If NA is put in this cell revert back to no formatting at all.

I have got the first three easily by:

1. =$H14=0
2. =$H14$G14
3. =$H14<=$G14

but what I cannot seem to do is amend the formula to get it to revert back
to white if NA is put in H14. Is there a way of doing this without using VB?

Hi Christina

conditional formatting is limited to three conditions, so here are two
non-VBA options for you to check out ...
1) Bob Phillips has an add-in available for download from his site

http://www.xldynamic.com/source/xld.....Download.html

2) John McGimpsey has notes on his site on how to do up to 6 without VBA

http://www.mcgimpsey.com/excel/conditional6.html

If neither of these are useful to you then you'll have to use VBA.

Cheers
JulieD
PS with your first formula both blank and 0 will pass the test ... if you
really just want blue cells when it is blank use
=$H14=""

"ChristinaC" wrote in message
...
I am trying to generate the following scenario:

1. If a cell is blank, colour blue
2. If a cell whose inserted date is less than or equal to the date in
another cell, colour green
3. If a cell whose inserted date is greater than the date in another
cell,
colour orange.
4. If NA is put in this cell revert back to no formatting at all.

I have got the first three easily by:

1. =$H14=0
2. =$H14$G14
3. =$H14<=$G14

but what I cannot seem to do is amend the formula to get it to revert back
to white if NA is put in H14. Is there a way of doing this without using
VB?

Here's an idea you may want to try since you seem to be familiar with
Conditional Formatting already and you only have need of "4" conditions.

Directly format the cells as what you are now thinking of as your "1st"
condition, i.e., empty cells filled with blue. (BTW- be certain you are clear
on the difference between an "empty" or "blank" cell and one that contains a
zero).

Use the three available Conditional Formatting conditions to handle the
other possibilities. If the cell content gets deleted or changed to something
that doesn't trigger one of those sets of CF, the cell will revert to it's
default (Blue).

Hope this is useful |:)

"ChristinaC" wrote:

I am trying to generate the following scenario:

1. If a cell is blank, colour blue
2. If a cell whose inserted date is less than or equal to the date in
another cell, colour green
3. If a cell whose inserted date is greater than the date in another cell,
colour orange.
4. If NA is put in this cell revert back to no formatting at all.

I have got the first three easily by:

1. =$H14=0
2. =$H14$G14
3. =$H14<=$G14

but what I cannot seem to do is amend the formula to get it to revert back
to white if NA is put in H14. Is there a way of doing this without using VB?