another conditional format issue

[RDOREMUS]

hello all,

i have a conditional format question about a simple spreadsheet.

what i have is 4 columns of expiring dates.

C D E I

1-jan-11 2-feb-12 3-mar-12 x


well what im having a problem with is that. . C:E is conditionally formatted off of a seperate column A, which is working the way i need it to. what i am looking for is for is a formula for when i have an X in column (I) that it makes C:E go to no color and if the X is taken away it goes back to its original conditional formatting based off of column A. Will i have to write a macro or is there a simpler way, because i have been trying all different types of formulas and have had no success at all. Thank you for your response.

 

[jasonb75]

That will highlight dates from april 2nd to april 15th if done today, is that what you want?

 

[RDOREMUS]

i want it to be like lets say the expiration date is April 30 2011 and when today hits apr 15 2011 it to change yellow all the way up to april 30th.

 

[jasonb75]

Personal preference is to use median to find a 'between' value

=AND(TODAY()=MEDIAN(TODAY(),C1,C1-15),$I1="x")

 

[RDOREMUS]

jason,

these are my CF formulas

red =IF(C1-TODAY()<0,1,0)=1

green =IF(C1-TODAY()>15,1,0)=1

yellow =(IF(AND(C1-TODAY()>0,C1-TODAY()<=15),1,0)=1

and for some reason when i test the dates out when the date gets into the parameters for yellow it goes blank instead of yellow

 

[RDOREMUS]

ok lemme try your formula its prolly better then mine

 

[jasonb75]

I just created a list of dates, and what you have appears to work correctly, are you applying the condition to the correct range?

Dates yesterday or before show red, today has no fill, 2nd to 16th april show yellow, 17th april onwards show green.

 

[RDOREMUS]

what formulas are you putting in for green and red, maybe its just my formulas cause when i put yours in two of the 3 cells will go clear and one will stay red, im kinda confused at this point to whats going wrong

 

[jasonb75]

I used the formula from your earlier post

red =IF(C1-TODAY()<0,1,0)=1

green =IF(C1-TODAY()>15,1,0)=1

yellow =(IF(AND(C1-TODAY()>0,C1-TODAY()<=15),1,0)=1

What range do you have the conditional format applied to?

 

[RDOREMUS]

C1:e1

 

[RDOREMUS]

ok i fixed it, it was def an error on my part. . so when i try to integrate your formula to make it to where i put an X in I it makes C:E clear it gives me an error message.