Conditional formatting with many if's and ands, etc.

[yybb44]

Hello All,
I don't even know if this is possible... I'm on Excel 2000.
Here is the image link:

http://img84.imageshack.us/my.php?image=examplelw7.jpg

What I want to accomplish:

When the number in Columns B and D are above the "Goal" number in row 8 for each individual column...highlight in blue.

BUT only if the letters in columns C and E are not "U, UJ, NJ, or JN"
Only highlight numbers that are attached to no letter at all or a J.

While we are at it....also if the numbers in Columns B and D are above the "Background" number in row 9 for each individual column...highlight the text in red.
and also if only the letters in columns C and E are not "U, UJ, NJ, or JN"
Only highlight numbers that are attached to no letter at all or a J.

Thank you in advance!!

 

[Gerald Higgins]

Hi Y, welcome to the board.
I think this can be done, but you need to be absolutely clear about what you want.
What if, on one row, 1 of the numbers is above goal, but the other one isn't, such as line 4 ? Do you only want them highlighted when BOTH numbers are above goal, or when AT LEAST ONE OF THEM is above goal ? Same for background.

 

[yybb44]

Thanks Gerald...

Basically each chemical has it's own goal and background. So in the end what I am looking for is that cells B2 and B4 will be both shaded blue and have red text because they are above the goal and background in cells B8 and B9.

For column D..I'm looking for cell D2 to be both shaded blue and have red text because it is above the numbers in D8 and D9.

Hope that cleared it up ???

Hi Y, welcome to the board.
I think this can be done, but you need to be absolutely clear about what you want.
What if, on one row, 1 of the numbers is above goal, but the other one isn't, such as line 4 ? Do you only want them highlighted when BOTH numbers are above goal, or when AT LEAST ONE OF THEM is above goal ? Same for background.

 

[Gerald Higgins]

Hi Y, yes I think that helps. I'm busy on something else just now but will aim to get something posted in half an hour or so.
In that time, someone else will probably have posted a solution.

 

[shemayisroel]

yybb44,

Would you be instered in a VBA solution or did you want it achieved via the Conditional formatting option?

 

[yybb44]

yybb44,

Would you be instered in a VBA solution or did you want it achieved via the Conditional formatting option?

Hi shemayisroel,

I have no issue with using VBA vs. conditional formatting. What I envision and hope for is something universal that can be used between various tables...something that will allow row numbers to be changed, etc.
But maybe my imagination is running wild...

Thanks

 

[Gerald Higgins]

Y - for a CF solution, this works for the data in your example.
Apply to cell B2, then copy the format to other cells as required.

Conditional Formating
Formula Is

=AND(B2>B$8,B2>B$9,OR(C2="",C2="J"))

Set format to blue or whatever, as required.

 

[yybb44]

Thanks Gerard!

It works great!...but there is something that throws a wrench into the solution....

If there happens to be a number that is in between the background and goal numbers..then the formula doesn't work. Maybe I should've provided an example showing that....sorry about that. I'll provide one right now....
http://img338.imageshack.us/my.php?image=example2jt2.jpg


So basically now....I would want B2 to have red text...and B4 to have both blue shading and red text. For column D..I would want D2 to have both shading and red text and D4 to just have blue shading....

Thanks in advance for your help

Y - for a CF solution, this works for the data in your example.
Apply to cell B2, then copy the format to other cells as required.

Conditional Formating
Formula Is

=AND(B2>B$8,B2>B$9,OR(C2="",C2="J"))

Set format to blue or whatever, as required.

 

[Gerald Higgins]

OK, then you need two separate conditions.

Condition 1
Formula Is

=AND(OR(AND(B2>B$8,B2< B$9),AND(B2< B$8,B2>B$9)),OR(C2="",C2="J"))

format as red

Condition 2
Formula is

=AND(B2>B$8,B2>B$9,OR(C2="",C2="J"))

format as blue.

This seems to work. I have to go to bed now but I'll look back tomorrow. Good luck !

By the way, I notice that Goal can be either lower or higher than background - my solution attempts to deal with this.

 

[yybb44]

Wow....this is exactly what I needed...and it worked out in all the example tests I'm throwing at it.

thank you thank you for your help...
if you ever end up in the San Francisco area I'll have to buy you a beer

OK, then you need two separate conditions.

Condition 1
Formula Is

=AND(OR(AND(B2>B$8,B2< B$9),AND(B2< B$8,B2>B$9)),OR(C2="",C2="J"))

format as red

Condition 2
Formula is

=AND(B2>B$8,B2>B$9,OR(C2="",C2="J"))

format as blue.

This seems to work. I have to go to bed now but I'll look back tomorrow. Good luck !

By the way, I notice that Goal can be either lower or higher than background - my solution attempts to deal with this.