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]

Try your conditional formats as below, making sure they are listed in the same order.

<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #ff0000; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #ff0000; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; BORDER-RIGHT-COLOR: #ff0000; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #ff0000"><TBODY><TR><TD>Conditional formatting </TD></TR><TR><TD><TABLE style="PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR><TD>Cell</TD><TD>Nr.: / Condition</TD><TD>Format</TD></TR><TR><TD>C1</TD><TD>1. / Formula is =AND(C1<TODAY(),$I1<>"x")</TD><TD style="BACKGROUND-COLOR: #ff0000">Abc</TD></TR><TR><TD>C1</TD><TD>2. / Formula is =AND(TODAY()=MEDIAN(TODAY(),C1,C1-15),$I1<>"x")</TD><TD style="BACKGROUND-COLOR: #ffff00">Abc</TD></TR><TR><TD>C1</TD><TD>3. / Formula is =AND(C1+15>TODAY(),$I1<>"x")</TD><TD style="BACKGROUND-COLOR: #00b050">Abc</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[RDOREMUS]

its prolly me but i dont understand that chart, do i type all that after my CF formulas or do they replace them, or do i only add some of that data to my CF formula?

 

[jasonb75]

Use those to replace what you have already, just noticed that the first one didn't display properly in the table.

Red condition formula =AND(C1 < TODAY(),$I1<>"x")

Yellow condition formula =AND(TODAY()=MEDIAN(TODAY(),C1,C1-15),$I1<>"x")

Green condition formula =AND(C1+15>TODAY(),$I1<>"x")

They must be in that order to work properly.

 

[RDOREMUS]

oh ok. . yea that first one kinda threw me off some. So ill try that out, thanks jason

 

[RDOREMUS]

it worked perfectly thank you so much for your patience and time.