Omitting blank cells from contitional formating

[tdcsi]

Using excel 2010. I have a spreadsheet that is basically a living document. I am trying to find a way to use conditional formatting to highlight cells that do not fall within a set range, but not highlight cells that are blank. This is the direction I am currently going:

=IF((F45>$F$7)*AND(F45<$F$8),FALSE,TRUE)

This does return the correct "True" result for this cell (F45 is the only cell that is outside my range) when used in the spreadsheet, but highlights all cells in the column when I try and plug it into conditional format. Also there is nothing to remove highlight from blank cells.

 

[SydneyGeek]

Use a formula in the conditional formatting. Something like (with the cursor in F45) --

=AND(F45 > $F$7,F45 < $F$8,NOT(ISBLANK(F45)))

Denis

 

[tdcsi]

Same issue I was having. Here is part of the sheet if it helps.

Excel Workbook
	F
7	8.00
8	9.00
9	0.94
10	1.06
11	8.36
12	8.70
13	8.04
14	9.20
15	0.49
16	0.67
17	8.39
18	8.66
19	0.27
20	Value
21	8.16
22	8.53
23	8.30
24	9.04
25	8.04
26
27
28
29
12070

Excel 2010

Cell Formulas
Range	Formula
F9	=F7/F1
F10	=F8/F1
F11	=F1*98%
F12	=F1*102%
F13	=MINA(F21:F1004)
F14	=MAXA(F21:F1004)
F15	=F1-F13
F16	=F14-F1
F17	=QUARTILE(F21:F1004,1)
F18	=QUARTILE(F21:F1004,3)
F19	=F18-F17

If it makes a difference, Cells F20:F10000 are set in a table and are the only cells I want formatted. but however I draw up a formula, it seems to act differently inside and outside the table.