Hello,

I have an existing spreadsheet that is used for different types of loan
accounts. Each of the different loan types are assigned a number designating
the type of loan it is. For examlpe; a residential loan may be a "type 3" and
a commercial loan a "type 5". The types can also be broken down further such
as a type 3 (residential loan) could have a payment type of a fixed or
adjustable interest loan. This spreadsheet uses Conditional Formatting for 2
different critieria; if a loan that is a type 3 (no matter what kind of
payment type) and has current loan balance of $500,000 to $999,999.99 then
the font in that row the font should be blue, if it is =1,000,000 then the
font is green. At the end of the spreadsheet there is a column that contains
a formula that also looks at the balance criteria and will enter the text of
"ILR" or "N-ILR" which determines who is responsible to review the loan.
Currently I have the following formula's in the Conditional Formatting dialog
boxes:
Condition 1: =AND($G2=500000,$G2<=999999.99,$N2=3) - blue font
Condition 2: =AND($G2=1000000,$N2=3) - green font
I was just asked to add a third criteria that if any type 3 loan that has a
payment type (payment types are contained in column E) of INTF that has a
balance =500,000 then the font in that row should be green. If I try to
simply add a 3rd criteria as Condition 3 within the CF dialog box with the
formula of:
=AND($G2=500000,$N2=3,$E2="INTF")
nothing happens because the 3rd condition is ignored as soon as the first
condition is met. I'm assuming that I need to create one formula within the
condition 1 field to accomplish what I need to do; but how can I do that?
What would the formula be. I hope I explained this issue without too much
confusion. If further clarification is needed please post it and I will
reply. Any help with this issue will be greatly appreciated. Thank you.

Dave Y

Am I missing something or why could this not be your first condition?


--
intruder9
------------------------------------------------------------------------
intruder9's Profile: http://www.excelforum.com/member.php...o&userid=30107
View this thread: http://www.excelforum.com/showthread...hreadid=524564

Dave
This combines your second and third criteria:

=OR(AND($G2=500000,$N2=3,$E2="INTF"),AND($G2=100 0000,$N2=3))
and format for green font.

And this allows thae first criterion to have an exception:

=AND($G2=500000,$G2<=999999.99,$N2=3,NOT($E2="INT F"))

HTH

Beege


"Dave Y" wrote in message
...
Hello,

I have an existing spreadsheet that is used for different types of loan
accounts. Each of the different loan types are assigned a number
designating
the type of loan it is. For examlpe; a residential loan may be a "type 3"
and
a commercial loan a "type 5". The types can also be broken down further
such
as a type 3 (residential loan) could have a payment type of a fixed or
adjustable interest loan. This spreadsheet uses Conditional Formatting for
2
different critieria; if a loan that is a type 3 (no matter what kind of
payment type) and has current loan balance of $500,000 to $999,999.99 then
the font in that row the font should be blue, if it is =1,000,000 then
the
font is green. At the end of the spreadsheet there is a column that
contains
a formula that also looks at the balance criteria and will enter the text
of
"ILR" or "N-ILR" which determines who is responsible to review the loan.
Currently I have the following formula's in the Conditional Formatting
dialog
boxes:
Condition 1: =AND($G2=500000,$G2<=999999.99,$N2=3) - blue font
Condition 2: =AND($G2=1000000,$N2=3) - green font
I was just asked to add a third criteria that if any type 3 loan that has
a
payment type (payment types are contained in column E) of INTF that has a
balance =500,000 then the font in that row should be green. If I try to
simply add a 3rd criteria as Condition 3 within the CF dialog box with the
formula of:
=AND($G2=500000,$N2=3,$E2="INTF")
nothing happens because the 3rd condition is ignored as soon as the first
condition is met. I'm assuming that I need to create one formula within
the
condition 1 field to accomplish what I need to do; but how can I do that?
What would the formula be. I hope I explained this issue without too much
confusion. If further clarification is needed please post it and I will
reply. Any help with this issue will be greatly appreciated. Thank you.

Dave Y

I'd have thought that for first condition you just need

=AND($N2=3,OR(AND($G2=500000,$E2="INTF"),$G2=100 0000,))

and format for green font.

Then simply for the second

=AND($G2=500000,$N2=3)

format blue


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=524564

Hi Beege,

Thanks you for your reply. The formula you provided seems to have worked
perfectly. I also want to thank intruder9 and daddylonglegs for their replies
as well. I greatly appreciate the help.

Dave Y

"Beege" wrote:

Dave
This combines your second and third criteria:

=OR(AND($G2=500000,$N2=3,$E2="INTF"),AND($G2=100 0000,$N2=3))
and format for green font.

And this allows thae first criterion to have an exception:

=AND($G2=500000,$G2<=999999.99,$N2=3,NOT($E2="INT F"))

HTH

Beege


"Dave Y" wrote in message
...
Hello,

I have an existing spreadsheet that is used for different types of loan
accounts. Each of the different loan types are assigned a number
designating
the type of loan it is. For examlpe; a residential loan may be a "type 3"
and
a commercial loan a "type 5". The types can also be broken down further
such
as a type 3 (residential loan) could have a payment type of a fixed or
adjustable interest loan. This spreadsheet uses Conditional Formatting for
2
different critieria; if a loan that is a type 3 (no matter what kind of
payment type) and has current loan balance of $500,000 to $999,999.99 then
the font in that row the font should be blue, if it is =1,000,000 then
the
font is green. At the end of the spreadsheet there is a column that
contains
a formula that also looks at the balance criteria and will enter the text
of
"ILR" or "N-ILR" which determines who is responsible to review the loan.
Currently I have the following formula's in the Conditional Formatting
dialog
boxes:
Condition 1: =AND($G2=500000,$G2<=999999.99,$N2=3) - blue font
Condition 2: =AND($G2=1000000,$N2=3) - green font
I was just asked to add a third criteria that if any type 3 loan that has
a
payment type (payment types are contained in column E) of INTF that has a
balance =500,000 then the font in that row should be green. If I try to
simply add a 3rd criteria as Condition 3 within the CF dialog box with the
formula of:
=AND($G2=500000,$N2=3,$E2="INTF")
nothing happens because the 3rd condition is ignored as soon as the first
condition is met. I'm assuming that I need to create one formula within
the
condition 1 field to accomplish what I need to do; but how can I do that?
What would the formula be. I hope I explained this issue without too much
confusion. If further clarification is needed please post it and I will
reply. Any help with this issue will be greatly appreciated. Thank you.

Dave Y