Conditional format cell if not blank

NadoDude

Board Regular
Joined
Jun 24, 2010
Messages
84
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?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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

Legend
Joined
Jun 19, 2002
Messages
63,650
I think that those 'blanks' must contain spaces. Anyway, try

Excel Workbook
A
1100
2230
3
Sheet3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A11. / Formula is =AND(ISNUMBER(A1),A1>213)Abc
 

NadoDude

Board Regular
Joined
Jun 24, 2010
Messages
84
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

Board Regular
Joined
Jun 24, 2010
Messages
84

ADVERTISEMENT

Thanks VoG. Your formula did the trick
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
If your cell is J3, you can use
Code:
=J3*(J3>213)

lenze
 

NadoDude

Board Regular
Joined
Jun 24, 2010
Messages
84

ADVERTISEMENT

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

Legend
Joined
Feb 18, 2002
Messages
13,690
2 Conditions
Cond1:
Code:
=F3*J3*(F3>180)
Cond2
Code:
=F3*(F3>213)

lenze
 

Watch MrExcel Video

Forum statistics

Threads
1,133,278
Messages
5,657,820
Members
418,414
Latest member
ECMdusty

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top