Conditional Formatting

[hrayani]

Hello Friends,

I need help with conditional formatting. I set two conditions on a cell that

if a cell value is lower then 0 then red
if a cell value is greater then 0 then green

conditional formatting is turning the cell green even if it returns text "OK" as an answer


I am using this formula
=IF(OR(ISBLANK($F68),ISBLANK(N68)),"",IF($F68=N68,"OK",N68-$F68))

There are 4 possible answers to that cell...
1) a value greater then 0
2) a value less then 0
3) a text "OK"
4) a blank cell ""


I would want the conditional formatting to only turn if red or green if the value is lower or greater then zero. Dont do anything if the cell returns a blank or a text "OK".

Any help would be appreciated,

Regards,

Humayun

 

[Scott T]

Clear your current conditional formatting and use

Change F3 to your cell

for green

=AND(ISNUMBER(F3),F3>0)

for red

=AND(ISNUMBER(F3),F3<0)

This will leave the cell unformatted for 0 if you want it to change you can modify the formula or add a different one if you want it to be a different color.

 

[stevembe]

I might be missing something here but I do not see why you are using a formula for what can be achieved using set rules:

In conditional formatting choose the option Format only cells that contain, then choose the greater than and less than options.

 

[hrayani]

Worked Great

Thanks

 

[Scott T]

I might be missing something here but I do not see why you are using a formula for what can be achieved using set rules:

In conditional formatting choose the option Format only cells that contain, then choose the greater than and less than options.

When you do that OK will trigger the conditional formatting. By using formulas the value can be tested to make sure it is a number since "Dont do anything if the cell returns a blank or a text "OK""

 

[stevembe]

When you do that OK will trigger the conditional formatting. By using formulas the value can be tested to make sure it is a number since "Dont do anything if the cell returns a blank or a text "OK""

Told you I may be missing something! Thanks Scott.