Remove Colour

[24849308]

I am already running three conditional formats but want to remove the colour red from cells that have no content. The formulas are:


=N47<TODAY()< p> (To make it go red)

=N47>TODAY()+29
(To make it go green)


= N47<TODAY()+30< p> (To make it go amber)

<TABLE style="WIDTH: 179pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=239 border=0 x:str><COLGROUP><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><TBODY><TR style="HEIGHT: 44.25pt; mso-height-source: userset" height=59><TD class=xl73 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 86pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 44.25pt; BACKGROUND-COLOR: transparent" width=115 height=59>Date Awarded</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 93pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=124>Review Date</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 179pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=239 border=0 x:str><COLGROUP><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><TBODY><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" width=115 height=21 x:num="40378">19-Jul-2010</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: #ffcc00; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" width=124 x:num="40743">19-Jul-2011</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" height=21 x:num="40197">19-Jan-2010</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BACKGROUND: red; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" x:num="40562">19-Jan-2011</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" height=21 x:num="40332">03-Jun-2010</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BACKGROUND: lime; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" x:num="41062">02-Jun-2012</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" height=21></TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BACKGROUND: red; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none"></TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" height=21></TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BACKGROUND: red; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none"></TD></TR></TBODY></TABLE>

 

[Rick Rothstein]

I don't think your complete "make it red" conditional formula came through, so I'll just use MakeItRedFormula as a stand-in for that formula (minus the equal sign, of course). Try your formula this way...

=AND(LEN(N47)>0,MakeItRedFormula)

You may have to do similarly for your other conditional formulas.

 

[24849308]

i don't think I explained myself too clearly. here goes for another attempt lol.

1. I am already running three conditional formats. These are as follows:

a. =O3<TODAY() This turns the cell red when the date is less than the current date.

b. =O3>TODAY()+29 This cell turns green when the date is greater than 29 days than the current date.

c. = O3<TODAY()+30 This cell turns amber when the date is within 30 days of the current date


<TABLE style="WIDTH: 179pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=239 border=0 x:str><COLGROUP><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><TBODY><TR style="HEIGHT: 44.25pt; mso-height-source: userset" height=59><TD class=xl73 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 86pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 44.25pt; BACKGROUND-COLOR: transparent" width=115 height=59>

N​

</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 93pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=124>

O​

</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 179pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=239 border=0 x:str><COLGROUP><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><TBODY><TR style="HEIGHT: 44.25pt; mso-height-source: userset" height=59><TD class=xl73 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 86pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 44.25pt; BACKGROUND-COLOR: transparent" width=115 height=59>Date Awarded</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 93pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=124>Review Date</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 179pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=239 border=0 x:str><COLGROUP><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><TBODY><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" width=115 height=21> </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: red; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" width=124> </TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" height=21 x:num="40378">19-Jul-2010</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BACKGROUND: #ffcc00; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" x:num="40743">19-Jul-2011</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" height=21 x:num="40197">19-Jan-2010</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BACKGROUND: red; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" x:num="40562">19-Jan-2011</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" height=21 x:num="40332">03-Jun-2010</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BACKGROUND: lime; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" x:num="41062">02-Jun-2012</TD></TR></TBODY></TABLE>

What I need in addition to these is something that will leave the 'O Column' cells that have no date in them clear.

Any sense........