Conditional Format help

[dennisthe10]

Hi
I have a date in H4 where using conditional formatting, will flag green if
its within 2 days of today, amber if 2-3 days of today and red if 3+ days
from today however, i need the formatting to stop if there has been a date
entered into I4 (as this will mean there is no need for the cell to flag to
us that there is no data in I4 if that makes sense)

can anyone help please?

thanks

Dennis

 

[Mike H]

What do you mean by

i need the formatting to stop if there has been a date
entered into I4

What colour should the cell be?
No colour or the colour it was when the date was entered into I4
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

This isn't very clear either.

I have a date in H4 where using conditional formatting, will flag green if
its within 2 days of today, amber if 2-3 days of today and red if 3+ days
from today however

Is it 2 days before or after the date in H4 or both?

Post you CF formulas
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[dennisthe10]

Hi Mike

Apologies for being unclear. I would need the cell to revert to no colour if
a date is added to the corresponding I cell.

The formatting is set so that the date entered in cell H4 changes colour
dependant on the difference from the date entered compared to todays date,
i.e. if the date entered was 15/03/10 (UK format) then it would change to
amber/orange to indicate 3 days have passed however, if a date is then
entered into I4 then i need it to revert back to no colour

hope this makes a bit more sense

thanks

 

[Mike H]

Hi,

You include an AND statement in the CF formula.

=AND(I4="",Your CF Formula)

this is an example for more than 2 days past

=AND(I4="",H4<TODAY()-2)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[dennisthe10]

Thanks Mike but no joy im afraid. I have already used the maximum 3
conditional formats, all set as Cell Value is between =today() and today()-1
etc then change colour

What would i need to have as the complete formula instead? or would it be
easier to run a macro to say if cell I4 contains data, turn of conditional
formatting in Cell H4?

thanks again (sorry to be a pain)