Conditional format cell if not blank

[NadoDude]

I have a conditional format on a range of cells that if the cell value is greater than 213 make cell red.

Some of the cells are blank, but the conditional format is turning the cell red.

Can I tell the conditional format to only format the cells with values?

 

[lenze]

There must be something else in your CF because a Blank cell is < 213. what CF condition(s) do you hace on this cell(s)?

lenze

 

[VoG]

I think that those 'blanks' must contain spaces. Anyway, try

Excel Workbook
	A
1	100
2	230
3
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =AND(ISNUMBER(A1),A1>213)	Abc

 

[NadoDude]

The cell value is a formula =IF(I3-H3=0,"",I3-H3)

I used the IF statement because I don't want to see the zeros.

 

[NadoDude]

Thanks VoG. Your formula did the trick

 

[lenze]

If your cell is J3, you can use

=J3*(J3>213)

lenze

 

[NadoDude]

lenze - like the short formula, thanks. Here is another problem:

<font size="2">Worksheet: Sheet2 UsedRange: $D$1:$K$3 Range: $D$1:$K$3</font><br /><br /><div style="border: 1px solid #666666; overflow: auto; width:580px; height: 220px;"><table border="1" cellspacing="2" cellpadding="3"><tr><td width="40px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2"> </font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">D</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">E</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">F</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">G</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">H</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">I</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">J</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">K</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><a name="cD1_705"></a><font size="2">TRIP 1</font></td><td width="100px" style="background-color:#ffffff;"><a name="cE1_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cF1_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cG1_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cH1_705"></a><font size="2">TRIP 2</font></td><td width="100px" style="background-color:#ffffff;"><a name="cI1_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ1_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK1_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">2</font></td><td width="100px" style="background-color:#ffffff;"><a name="cD2_705"></a><font size="2">Depart</font></td><td width="100px" style="background-color:#ffffff;"><a name="cE2_705"></a><font size="2">Return</font></td><td width="100px" style="background-color:#ffffff;"><a name="cF2_705"></a><font size="2">Length</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG2_705"></a><font size="2">DWELL</font></td><td width="100px" style="background-color:#ffffff;"><a name="cH2_705"></a><font size="2">Depart</font></td><td width="100px" style="background-color:#ffffff;"><a name="cI2_705"></a><font size="2">Return</font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ2_705"></a><font size="2">Length</font></td><td width="100px" style="background-color:#ffffff;"><a name="cK2_705"></a><font size="2">DWELL</font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">3</font></td><td width="100px" style="background-color:#ffffff;"><a name="cD3_705"></a><font size="2">8/26/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cE3_705"></a><font size="2">2/26/2011</font></td><td width="100px" style="background-color:#ffffff;"><a name="cF3_705"></a><font size="2"><a title="=E3-D3" href="#fF3_705">184</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cG3_705"></a><font size="2"><a title="=IF(ISERROR((D3-C3)/(C3-B3)),3,(D3-C3)/(C3-B3))" href="#fG3_705">1</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cH3_705"></a><font size="2">10/21/2011</font></td><td width="100px" style="background-color:#ffffff;"><a name="cI3_705"></a><font size="2">4/20/2012</font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ3_705"></a><font size="2"><a title="=IF(I3-H3=0,"",I3-H3)" href="#fJ3_705">182</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK3_705"></a><font size="2"><a title="=(H3-E3)/(E3-D3)" href="#fK3_705">0.972093023255814</a></font></td></table></div><p /><div style="border: 1px solid #666666; overflow: auto; width:580px; height: 220px;"><table border="1" cellspacing="2" cellpadding="3"><tr><td width="40px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">Address</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">Value</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">Formula</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fF3_705"></a><font size="2"><a href="#cF3_705">F3</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">184</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=E3-D3</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fG3_705"></a><font size="2"><a href="#cG3_705">G3</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF(ISERROR((D3-C3)/(C3-B3)),3,(D3-C3)/(C3-B3))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fJ3_705"></a><font size="2"><a href="#cJ3_705">J3</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">182</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF(I3-H3=0,"",I3-H3)</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fK3_705"></a><font size="2"><a href="#cK3_705">K3</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">0.972093023255814</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=(H3-E3)/(E3-D3)</font></td></tr></table></div>

In the sheet above column F is conditionally formatted to go red if the number is higher than 213. Column J goes red if greater than 180.

However - Here are the business rules: If there is only one trip, the length (column F) cannot exceed 213. If there are two trips on the same record, the length (column F) cannot exceed 180 days.

So my problem is - if there is no second trip and the value in F is greater than 213: turn red. If there is a second trip and the value in F is greater than 180: turn red.

 

[lenze]

2 Conditions
Cond1:

=F3*J3*(F3>180)

Cond2

=F3*(F3>213)

lenze

 

[NadoDude]

Perfect - Thanks Lenze