A Nice Easy One (Hopefully!)

Mr Nick · Mar 14th, 2002, 08:25 AM

I want to apply conditional formatting to a cell so that it is filled in red if the value is above a set value (9.6). Easy enough. However, when using the "Greater Than" condition, the cell is formatted if it has no value entered. How do I stop this happening?

pilot · Mar 14th, 2002, 08:38 AM

I could not duplicate this happening (blank did not trigger conditional formatting). Are you sure the cell is otherwise empty?

Paul B · Mar 14th, 2002, 08:38 AM

Nick greater than 9.6 works for me, anything above 9.6 changes the cell condition anyhing under does not

Quote:

On 2002-03-14 07:25, Mr Nick wrote:
I want to apply conditional formatting to a cell so that it is filled in red if the value is above a set value (9.6). Easy enough. However, when using the "Greater Than" condition, the cell is formatted if it has no value entered. How do I stop this happening?

Mr Nick · Mar 14th, 2002, 08:42 AM

It contains a formula:

=IF(G12="","",IF(G12="FIXED POWER",SUM(((T12*C12*K12)/1000)+U12),IF(G12="LIGHTING",SUM(((T12*C12*K12)/1000)+U12),IF(G12="RING MAIN",SUM(((T12*C12*K12)/4000)+U12),"Cct Type!"))))

Which, if certain cells are not filled in, leaves this conditionally formatted cell blank. If it left blank, the cell gets formatted but if value is entered above 0 the formatting works. I've tried adding a second condition for a zero value but this doesn't work either??

JIMMARSHALL6660 · Mar 14th, 2002, 08:45 AM

[quote]
On 2002-03-14 07:25, Mr Nick wrote:
I want to apply conditional formatting to a cell so that it is filled in red if the value is above a set value (9.6). Easy enough. However, when using the "Greater Than" condition, the cell is formatted if it has no value entered. How do I stop this happening?
[Hi Mr Nick,
You require to set the first value on the conditional formatting equation to equal 9.6 = no format and then set a second condition of greater than

]

Mr Nick · Mar 14th, 2002, 08:49 AM

That doesn't work. It still formats the cell in red if it is blank.

smozgur · Mar 14th, 2002, 09:01 AM

Just select

"Formula is"

in conditional formatting then use this for formula

=AND(ISNUMBER(B3),B3>9.6)

(=AND(ISNUMBER(B3);B3>9,6) if you are using , for decimal seperator in your system)

B3 is the cell reference

regards
suat
(You can visit TheWordExpert for VBA help and also other office applications)

[ This Message was edited by: smozgur on 2002-03-14 08:02 ]

pilot · Mar 14th, 2002, 09:06 AM

You might try setting the conditional formatting to Formula Is =(your formula)>9.6

Mr Nick · Mar 14th, 2002, 09:10 AM

Smozgur - That's done it.

Cheers Everybody.

smozgur · Mar 14th, 2002, 09:13 AM

Nice to hear that

suat

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 14th, 2002, 08:25 AM	#1
Mr Nick Board Regular Join Date: Mar 2002 Location: Manchester, UK Posts: 95	I want to apply conditional formatting to a cell so that it is filled in red if the value is above a set value (9.6). Easy enough. However, when using the "Greater Than" condition, the cell is formatted if it has no value entered. How do I stop this happening?

Mar 14th, 2002, 08:38 AM	#2
pilot Board Regular Join Date: Feb 2002 Location: Houston, TX Posts: 303	I could not duplicate this happening (blank did not trigger conditional formatting). Are you sure the cell is otherwise empty?

Mar 14th, 2002, 08:42 AM	#4
Mr Nick Board Regular Join Date: Mar 2002 Location: Manchester, UK Posts: 95	It contains a formula: =IF(G12="","",IF(G12="FIXED POWER",SUM(((T12C12K12)/1000)+U12),IF(G12="LIGHTING",SUM(((T12C12K12)/1000)+U12),IF(G12="RING MAIN",SUM(((T12C12K12)/4000)+U12),"Cct Type!")))) Which, if certain cells are not filled in, leaves this conditionally formatted cell blank. If it left blank, the cell gets formatted but if value is entered above 0 the formatting works. I've tried adding a second condition for a zero value but this doesn't work either??

Mar 14th, 2002, 08:45 AM	#5
JIMMARSHALL6660 New Member Join Date: Mar 2002 Location: Glasgow Posts: 12	[quote] On 2002-03-14 07:25, Mr Nick wrote: I want to apply conditional formatting to a cell so that it is filled in red if the value is above a set value (9.6). Easy enough. However, when using the "Greater Than" condition, the cell is formatted if it has no value entered. How do I stop this happening? [Hi Mr Nick, You require to set the first value on the conditional formatting equation to equal 9.6 = no format and then set a second condition of greater than ]

Mar 14th, 2002, 08:49 AM	#6
Mr Nick Board Regular Join Date: Mar 2002 Location: Manchester, UK Posts: 95	That doesn't work. It still formats the cell in red if it is blank.

Mar 14th, 2002, 09:01 AM	#7
smozgur BatCoder Join Date: Feb 2002 Location: Turkey Posts: 764	Just select "Formula is" in conditional formatting then use this for formula =AND(ISNUMBER(B3),B3>9.6) (=AND(ISNUMBER(B3);B3>9,6) if you are using , for decimal seperator in your system) B3 is the cell reference regards suat (You can visit TheWordExpert for VBA help and also other office applications) [ This Message was edited by: smozgur on 2002-03-14 08:02 ]

Mar 14th, 2002, 09:06 AM	#8
pilot Board Regular Join Date: Feb 2002 Location: Houston, TX Posts: 303	You might try setting the conditional formatting to Formula Is =(your formula)>9.6

Mar 14th, 2002, 09:10 AM	#9
Mr Nick Board Regular Join Date: Mar 2002 Location: Manchester, UK Posts: 95	Smozgur - That's done it. Cheers Everybody.

Mar 14th, 2002, 09:13 AM	#10
smozgur BatCoder Join Date: Feb 2002 Location: Turkey Posts: 764	Nice to hear that suat