• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

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
0
cpatte7372
Asked:
cpatte7372
1 Solution
 
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now