A Nice Easy One (Hopefully!)

Mr Nick

Board Regular
Joined
Mar 11, 2002
Messages
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?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
I could not duplicate this happening (blank did not trigger conditional formatting). Are you sure the cell is otherwise empty?
 
Upvote 0
Nick greater than 9.6 works for me, anything above 9.6 changes the cell condition anyhing under does not

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?
 
Upvote 0
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??
 
Upvote 0
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


]
 
Upvote 0
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
 
Upvote 0
You might try setting the conditional formatting to Formula Is =(your formula)>9.6
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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
Back
Top