I am sure I am making it harder than it is. I ham banging my head on a wall after 2 hours of trying to figure this out with only failing attempts trying multiple formulas and conditional formatting I want cell F12 to have the result of the word YES or NO based on cell E12

If E12 is less than zero (negative amount) then F12 should be NO

If E12 is greater than zero (positive amount) then F12 should be YESOR

If E12 is highlighted red then F12 should be NO

iF E12 is highlighted green then F12 should be YESI prefer the 1st option but am willing to color code to get the result I need.

Please help

The formula method is pretty straight forward, with one minor issue: You specified NO for less than zero and YES for greater than zero, but it is not clear what you want as a result when E12 equalszero.This formula should meet your requirements and return a blank cell if E12 = 0

=IF(E12<0,"NO",IF(E12>0,"YES",""))

These formulas will return NO only when E12 is less than zero.

=IF(E12<0,"NO","YES")

=IF(E12>0,"YES","NO")Basing the result in F12 on the color of E12 is a bit more difficult. There are 2 ways (that I know of) to have a specific value (string or number) appear in a cell based on the format of another cell.

1 - Use a macro. The macro could monitor the fill color (or condition that set the fill color) of E12 and then put YES or NO in F12 based on what it sees.

2 - Use Conditional Formatting to hide part of a string by using 2 rules. Here's what I mean:

First, put

Yes Noin F12 and manually change the font color of each word so that it looks like this:YES NO

Then use these 2 Conditional Formatting rules on F12:

=E12<0 (Fill with Red)

=E12>0 (Fill with Green)Based on the value in E12, the Conditional Formatting fill color of F12 will hide either the Green word (NO) or the Red word (YES).

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

See how this works: =IF(E12<0,"No","Yes")

If E12 is less than Zero, then NO

If E12 is

notless than Zero, IE any positive number including Zero

then the answer must be YESIf you want Zero to be included in the NO answers then this:

=IF(E12<=0,"No","Yes")

If E12 is less than or equal to Zero, then NO

MIKE

Time to pick a nit. ;-) re:

any positive numberincludingZeroPerhaps you meant to say

any positive number.andZeroUnless you live is France, positive numbers don't typically

zero.includeHere is an interesting discussion regarding the designation of zero as a positive, negative or unsigned number:

https://math.stackexchange.com/ques...

(Just having some fun here!)

message edited by DerbyDad03

Ask Your Question

Weekly Poll

Do you agree with Google's decision to shut down Google+?

Discuss in The Lounge

Poll History