Conditional Formatting with Excel - Part 6

Hi Experts,

Hopefully, I'll be done with conditional formatting after this request.

I've trying to conditionally format cells A2 through to A200 to highlight when a certain condition occurs(please see attached). Unfortunately, there is an error in my formulas.

I've tried both of the following:

=AND(F2<C2,J2<G2,G2<C2,J2<F2,G2>F2,N2<K2,K2<G2,N2<J2,K2>J2,R2>O2,(R2-O2>(P2-R2),(O2-Q2)>(R2-O2)*1.5),V2>S2,V2>R2,S2>O2,S2>K2)

=AND(F2<C2),(J2<G2,G2<C2,J2<F2,G2>F2),(N2<K2,K2<G2,N2<J2,K2>J2),(R2>O2,(R2-O2)>(P2-R2),(O2-Q2)>(R2-O2)*1.5),(V2>S2,V2>R2,S2>O2,S2>K2)

But I keep on getting error message with my formula.

I wonder if an expert could take a look and let me know where I might be going wrong

Cheers

Carlotn
Hammerv2.xlsm
cpatte7372Asked:
Who is Participating?
 
StephenJRCommented:
Try this, one too many left brackets:

=AND(H2<D2,L2<H2,R2>O2,(R2-O2)>(P2-R2),(O2-Q2)>(R2-O2)*1.5)
0
 
StephenJRCommented:
Do these do the right thing? You had some missing (as well as superfluous) brackets.

=AND(F2<C2,J2<G2,G2<C2,J2<F2,G2>F2,N2<K2,K2<G2,N2<J2,K2>J2,R2>O2,(R2-O2)>(P2-R2),((O2-Q2)>(R2-O2)*1.5),V2>S2,V2>R2,S2>O2,S2>K2)

=AND(F2<C2,J2<G2,G2<C2,J2<F2,G2>F2,N2<K2,K2<G2,N2<J2,K2>J2,R2>O2,(R2-O2)>(P2-R2),((O2-Q2)>(R2-O2)*1.5),V2>S2,V2>R2,S2>O2,S2>K2)
0
 
cpatte7372Author Commented:
Stephen,

Thanks for responding, I'm going to check it now...
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
cpatte7372Author Commented:
Stephen,

Unfortunately it doesn't work.

I appreciate that my formula is probably incorrect. However, what I'm trying to do with this formula is calculate each bracketed area seperately. Does that make sense?

=AND(F2<C2),(J2<G2,G2<C2,J2<F2,G2>F2),(N2<K2,K2<G2,N2<J2,K2>J2),((R2>O2,(R2-O2)>(P2-R2),(O2-Q2)>(R2-O2)*1.5)),(V2>S2,V2>R2,S2>O2,S2>K2)


Cheers
0
 
StephenJRCommented:
I don't that brackets will make any difference with AND. I think

AND(1>2,(2>3,3>4))

is the same as

AND(1>2,2>3,3>4)

because as soon as single statement is False the whole statement will evaluate to False.

Perhaps you need OR? Can you explain what you are trying to do?
0
 
barry houdiniCommented:
What do you mean "calculate each bracketed area separately"?

Within an AND function every condition needs to be TRUE for the whole thing to be TRUE, so if you have

=AND(A1>B1,D1>E1,X1>Y1)

that means that the result is only TRUE if each of the 3 conditions is TRUE

regards, barry

0
 
cpatte7372Author Commented:
OK,

Give me second I will show what your formula produces....
0
 
cpatte7372Author Commented:
Ok,

One of the conditions is that the value in cell J should be lower than the cell value in cell G. However if you look at cell J151, its higher than cell G151, yet I get a positive condition a highlighted in cell A151.


Hammerv3.xlsm
0
 
StephenJRCommented:
I think the CF is coming from your other condition.
0
 
StephenJRCommented:
I'm guessing that may be a mistake as it only seems to apply to row 151 alone.
0
 
cpatte7372Author Commented:
Stephen,

I think you might be right. I need to run the conditional on different numbers. I'm going to check it out on different data now.

In meantime, can you see any reason why this formula doesn't work?

=AND(H2<D2,L2<H2,R2>O2,(R2>O2,(R2-O2)>(P2-R2),(O2-Q2)>(R2-O2)*1.5)

Cheers
0
 
StephenJRCommented:
Not sure, what you mean by not work? Not produce the expected result or produce an error?

As we were saying above, this could be simplified to:

=AND(H2<D2,L2<H2,R2>O2,(R2-O2)>(P2-R2),(O2-Q2)>(R2-O2)*1.5)
0
 
cpatte7372Author Commented:
Stephen

I meant to say can you see any reason why the following formula won't work:

=AND(H2<D2,L2<H2,(R2>O2,(R2-O2)>(P2-R2),(O2-Q2)>(R2-O2)*1.5)

Cheers
0
 
cpatte7372Author Commented:
Stephen,

That worked. Cheers mate.
0
 
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.