Conditional formatting using nested "Or(and ..."
 

I'm using nested conditions as follows:
=OR(AND(F7="Y",K7=""),AND(H7="Y",K7=""),AND(AS7="Y es",AF7="",K7<"X",K7<"*"),AND(I7="$",AF7="")),
where if the conditions (AND(F7="Y",K7="") are true cell turns red, or if
the second conditions are true AND(H7="Y",K7="") the cell turns red, etc. ie,
I want each "AND" condition to be evaluated separately, so if any of the AND
conditions are true the cell will turn red. I must have my ( ) in the wrong
combinations because the formula does not produce my expected results.

Conditional formatting using nested "Or(and ..."
 

What results is it producing? I think you are having difficult with the
portion...
AND(AS7="Yes",AF7="",K7<"X",K7<"*")
And my guess is that it is K7<"*", is it suppose to be an asterisk?
you might try changing it to K7<CODE(42)

If this doesn't help, respond back and describe which conditions work, and
which ones don't.
--
John C


"Gary" wrote:

I'm using nested conditions as follows:
=OR(AND(F7="Y",K7=""),AND(H7="Y",K7=""),AND(AS7="Y es",AF7="",K7<"X",K7<"*"),AND(I7="$",AF7="")),
where if the conditions (AND(F7="Y",K7="") are true cell turns red, or if
the second conditions are true AND(H7="Y",K7="") the cell turns red, etc. ie,
I want each "AND" condition to be evaluated separately, so if any of the AND
conditions are true the cell will turn red. I must have my ( ) in the wrong
combinations because the formula does not produce my expected results.

Conditional formatting using nested "Or(and ..."
 

There's nothing wrong with the syntax of your formula.

--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
I'm using nested conditions as follows:
=OR(AND(F7="Y",K7=""),AND(H7="Y",K7=""),AND(AS7="Y es",AF7="",K7<"X",K7<"*"),AND(I7="$",AF7="")),
where if the conditions (AND(F7="Y",K7="") are true cell turns red, or if
the second conditions are true AND(H7="Y",K7="") the cell turns red, etc.
ie,
I want each "AND" condition to be evaluated separately, so if any of the
AND
conditions are true the cell will turn red. I must have my ( ) in the
wrong
combinations because the formula does not produce my expected results.

Conditional formatting using nested "Or(and ..."
 

Something must be wrong. Here's my reasoning using part of my formula as
follows: =OR(AND(F7="Y",K7=""),AND(H7="Y",K7=""))
If I use this part of the above formula AND(F7="Y",K7="") the cell K7 turns
red. However, if I add the second part, ie AND(H7="Y",K7="")) without
changing any values in any cells, the cell K7 does not turn red. If I enter
"Y" in cell H7, so both cells F7 and H7 = "Y", the cell K7 turns red. I
believe my formula is evaluating both of the And statements together, instead
of independently of each other. I assume there must be some way of arranging
the ( ) so each AND statement is evaluated independently so if either AND
statement is true, cell K7 will turn red.

"T. Valko" wrote:

There's nothing wrong with the syntax of your formula.

--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
I'm using nested conditions as follows:
=OR(AND(F7="Y",K7=""),AND(H7="Y",K7=""),AND(AS7="Y es",AF7="",K7<"X",K7<"*"),AND(I7="$",AF7="")),
where if the conditions (AND(F7="Y",K7="") are true cell turns red, or if
the second conditions are true AND(H7="Y",K7="") the cell turns red, etc.
ie,
I want each "AND" condition to be evaluated separately, so if any of the
AND
conditions are true the cell will turn red. I must have my ( ) in the
wrong
combinations because the formula does not produce my expected results.

Conditional formatting using nested "Or(and ..."
 

It works fine for me. Do you have any hidden characters per chance?
--
John C


"Gary" wrote:

Something must be wrong. Here's my reasoning using part of my formula as
follows: =OR(AND(F7="Y",K7=""),AND(H7="Y",K7=""))
If I use this part of the above formula AND(F7="Y",K7="") the cell K7 turns
red. However, if I add the second part, ie AND(H7="Y",K7="")) without
changing any values in any cells, the cell K7 does not turn red. If I enter
"Y" in cell H7, so both cells F7 and H7 = "Y", the cell K7 turns red. I
believe my formula is evaluating both of the And statements together, instead
of independently of each other. I assume there must be some way of arranging
the ( ) so each AND statement is evaluated independently so if either AND
statement is true, cell K7 will turn red.

"T. Valko" wrote:

There's nothing wrong with the syntax of your formula.

--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
I'm using nested conditions as follows:
=OR(AND(F7="Y",K7=""),AND(H7="Y",K7=""),AND(AS7="Y es",AF7="",K7<"X",K7<"*"),AND(I7="$",AF7="")),
where if the conditions (AND(F7="Y",K7="") are true cell turns red, or if
the second conditions are true AND(H7="Y",K7="") the cell turns red, etc.
ie,
I want each "AND" condition to be evaluated separately, so if any of the
AND
conditions are true the cell will turn red. I must have my ( ) in the
wrong
combinations because the formula does not produce my expected results.

Conditional formatting using nested "Or(and ..."
 

To debug your formula, copy it from your CF "Formula Is" window and paste to
a separate cell. If need be you can split it into manageable chunks in
extra cells.
Copy and paste, don't try to retype, as your problem is presumably a typing
error somewhere.
--
David Biddulph

"Gary" wrote in message
...
Something must be wrong. Here's my reasoning using part of my formula as
follows: =OR(AND(F7="Y",K7=""),AND(H7="Y",K7=""))
If I use this part of the above formula AND(F7="Y",K7="") the cell K7
turns
red. However, if I add the second part, ie AND(H7="Y",K7="")) without
changing any values in any cells, the cell K7 does not turn red. If I
enter
"Y" in cell H7, so both cells F7 and H7 = "Y", the cell K7 turns red. I
believe my formula is evaluating both of the And statements together,
instead
of independently of each other. I assume there must be some way of
arranging
the ( ) so each AND statement is evaluated independently so if either AND
statement is true, cell K7 will turn red.

"T. Valko" wrote:

There's nothing wrong with the syntax of your formula.

--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
I'm using nested conditions as follows:
=OR(AND(F7="Y",K7=""),AND(H7="Y",K7=""),AND(AS7="Y es",AF7="",K7<"X",K7<"*"),AND(I7="$",AF7="")),
where if the conditions (AND(F7="Y",K7="") are true cell turns red, or
if
the second conditions are true AND(H7="Y",K7="") the cell turns red,
etc.
ie,
I want each "AND" condition to be evaluated separately, so if any of
the
AND
conditions are true the cell will turn red. I must have my ( ) in the
wrong
combinations because the formula does not produce my expected results.

Conditional formatting using nested "Or(and ..."
 

I'm not familiar with hidden characters. How would I determine if there are
any hidden characters? I have used Cell Formatting to "clear cell contents".

"John C" wrote:

It works fine for me. Do you have any hidden characters per chance?
--
John C


"Gary" wrote:

Something must be wrong. Here's my reasoning using part of my formula as
follows: =OR(AND(F7="Y",K7=""),AND(H7="Y",K7=""))
If I use this part of the above formula AND(F7="Y",K7="") the cell K7 turns
red. However, if I add the second part, ie AND(H7="Y",K7="")) without
changing any values in any cells, the cell K7 does not turn red. If I enter
"Y" in cell H7, so both cells F7 and H7 = "Y", the cell K7 turns red. I
believe my formula is evaluating both of the And statements together, instead
of independently of each other. I assume there must be some way of arranging
the ( ) so each AND statement is evaluated independently so if either AND
statement is true, cell K7 will turn red.

"T. Valko" wrote:

There's nothing wrong with the syntax of your formula.

--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
I'm using nested conditions as follows:
=OR(AND(F7="Y",K7=""),AND(H7="Y",K7=""),AND(AS7="Y es",AF7="",K7<"X",K7<"*"),AND(I7="$",AF7="")),
where if the conditions (AND(F7="Y",K7="") are true cell turns red, or if
the second conditions are true AND(H7="Y",K7="") the cell turns red, etc.
ie,
I want each "AND" condition to be evaluated separately, so if any of the
AND
conditions are true the cell will turn red. I must have my ( ) in the
wrong
combinations because the formula does not produce my expected results.

Conditional formatting using nested "Or(and ..."
 

If you cleared contents, you should be good, there are some characters that
don't necessarily display anything (such as a carriage return), and sometimes
the font is formatted to the same color of the background.

I have created the exact conditions you specify, and K7 changes to red for
each possibility, and for multiple possibilities..
--
John C


"Gary" wrote:

I'm not familiar with hidden characters. How would I determine if there are
any hidden characters? I have used Cell Formatting to "clear cell contents".

"John C" wrote:

It works fine for me. Do you have any hidden characters per chance?
--
John C


"Gary" wrote:

Something must be wrong. Here's my reasoning using part of my formula as
follows: =OR(AND(F7="Y",K7=""),AND(H7="Y",K7=""))
If I use this part of the above formula AND(F7="Y",K7="") the cell K7 turns
red. However, if I add the second part, ie AND(H7="Y",K7="")) without
changing any values in any cells, the cell K7 does not turn red. If I enter
"Y" in cell H7, so both cells F7 and H7 = "Y", the cell K7 turns red. I
believe my formula is evaluating both of the And statements together, instead
of independently of each other. I assume there must be some way of arranging
the ( ) so each AND statement is evaluated independently so if either AND
statement is true, cell K7 will turn red.

"T. Valko" wrote:

There's nothing wrong with the syntax of your formula.

--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
I'm using nested conditions as follows:
=OR(AND(F7="Y",K7=""),AND(H7="Y",K7=""),AND(AS7="Y es",AF7="",K7<"X",K7<"*"),AND(I7="$",AF7="")),
where if the conditions (AND(F7="Y",K7="") are true cell turns red, or if
the second conditions are true AND(H7="Y",K7="") the cell turns red, etc.
ie,
I want each "AND" condition to be evaluated separately, so if any of the
AND
conditions are true the cell will turn red. I must have my ( ) in the
wrong
combinations because the formula does not produce my expected results.