Colouring Ranges & Leaving Cells Blank
 

Hi all,

I have dates in columns L & K, in column M I calculate the difference
between these two dates.

Valid number of days btwn dates would be 1 to 100. If the difference is
less than 1 day or is more than 100 days, I want to flag the cells somehow.

I have tried:
Under Format Cells General Custom,
[Red][<1]General;[Blue][100]General
- this works great but the valid numbers disappear (i.e., numbers from 1 to
100).

I tried
[Red][<1]General;[Blue][100]General;[Black][0]General;[Black][<101]General
- and I get the error "Excel cannot use the number format you typed"

Alternatively, I would be happy to do conditional formatting for the cells
with values less than 1 to be pink and above 100 to be green. I can do this
and the numbers all show up but then the invalid numbers (like -39191) show
up too and it's ugly. Is there a way to have the conditional formatting color
show for cells outside of the range but the cell is left blank?

tia for any suggestions,
karen

Colouring Ranges & Leaving Cells Blank
 

sorry forgot to mention working in Excel 2000 on stand alone XP

"klam" wrote:

Hi all,

I have dates in columns L & K, in column M I calculate the difference
between these two dates.

Valid number of days btwn dates would be 1 to 100. If the difference is
less than 1 day or is more than 100 days, I want to flag the cells somehow.

I have tried:
Under Format Cells General Custom,
[Red][<1]General;[Blue][100]General
- this works great but the valid numbers disappear (i.e., numbers from 1 to
100).

I tried
[Red][<1]General;[Blue][100]General;[Black][0]General;[Black][<101]General
- and I get the error "Excel cannot use the number format you typed"

Alternatively, I would be happy to do conditional formatting for the cells
with values less than 1 to be pink and above 100 to be green. I can do this
and the numbers all show up but then the invalid numbers (like -39191) show
up too and it's ugly. Is there a way to have the conditional formatting color
show for cells outside of the range but the cell is left blank?

tia for any suggestions,
karen