conditional formating
 

I am using Excel 2007 and would like to create certain rules and cannot
figure out how to do using conditional format. Lets say I have a typical
spreadsheet. Here is the example I am looking for. Lets say A4 needs to
have a certain fill color if in J4 through n4 contains the words "help"
twice. Then I need A4 to change its fill color if J4 through N4 contain the
word "done" once. I have tried several things but cannot figure out.

conditional formating
 

Try CF formulae of

=COUNTIF(A4:J4,"help")=2

and

=COUNTIF(J4:N4,"done")=2

--
__________________________________
HTH

Bob

"Brent" wrote in message
...
I am using Excel 2007 and would like to create certain rules and cannot
figure out how to do using conditional format. Lets say I have a typical
spreadsheet. Here is the example I am looking for. Lets say A4 needs to
have a certain fill color if in J4 through n4 contains the words "help"
twice. Then I need A4 to change its fill color if J4 through N4 contain
the
word "done" once. I have tried several things but cannot figure out.

conditional formating
 

Select A4 and then call up the Conditional Formatting "New Rule" dialog. You
will need two rules and both of them will use the "Use a formula to
determine which cells to format" option in the "Select a Rule Type" listing.
These are the two formulas you would use in the "Format values where this
formula is true" field (for each rule individually)...

=COUNTIF(J4:N4,"done")=1

=COUNTIF(J4:N4,"help")=2

--
Rick (MVP - Excel)


"Brent" wrote in message
...
I am using Excel 2007 and would like to create certain rules and cannot
figure out how to do using conditional format. Lets say I have a typical
spreadsheet. Here is the example I am looking for. Lets say A4 needs to
have a certain fill color if in J4 through n4 contains the words "help"
twice. Then I need A4 to change its fill color if J4 through N4 contain
the
word "done" once. I have tried several things but cannot figure out.

conditional formating
 

Thanks for the feedback. I got this to work, but I have one more question.
Now I would like a4 to change to a different color if the following is met.
row J4 thru N4 has "help" twice and "done" once. Is there a way to combine
if both criteria have been met?

"Rick Rothstein" wrote:

Select A4 and then call up the Conditional Formatting "New Rule" dialog. You
will need two rules and both of them will use the "Use a formula to
determine which cells to format" option in the "Select a Rule Type" listing.
These are the two formulas you would use in the "Format values where this
formula is true" field (for each rule individually)...

=COUNTIF(J4:N4,"done")=1

=COUNTIF(J4:N4,"help")=2

--
Rick (MVP - Excel)


"Brent" wrote in message
...
I am using Excel 2007 and would like to create certain rules and cannot
figure out how to do using conditional format. Lets say I have a typical
spreadsheet. Here is the example I am looking for. Lets say A4 needs to
have a certain fill color if in J4 through n4 contains the words "help"
twice. Then I need A4 to change its fill color if J4 through N4 contain
the
word "done" once. I have tried several things but cannot figure out.

conditional formating
 

Is that in addition to the first two I gave you or in place of the first two
I gave you? Also, how many criteria will you ultimately want on that one
cell (there are limitations as to how many different conditional formats you
can have on a single range).

--
Rick (MVP - Excel)


"Brent" wrote in message
...
Thanks for the feedback. I got this to work, but I have one more
question.
Now I would like a4 to change to a different color if the following is
met.
row J4 thru N4 has "help" twice and "done" once. Is there a way to
combine
if both criteria have been met?

"Rick Rothstein" wrote:

Select A4 and then call up the Conditional Formatting "New Rule" dialog.
You
will need two rules and both of them will use the "Use a formula to
determine which cells to format" option in the "Select a Rule Type"
listing.
These are the two formulas you would use in the "Format values where this
formula is true" field (for each rule individually)...

=COUNTIF(J4:N4,"done")=1

=COUNTIF(J4:N4,"help")=2

--
Rick (MVP - Excel)


"Brent" wrote in message
...
I am using Excel 2007 and would like to create certain rules and cannot
figure out how to do using conditional format. Lets say I have a
typical
spreadsheet. Here is the example I am looking for. Lets say A4 needs
to
have a certain fill color if in J4 through n4 contains the words "help"
twice. Then I need A4 to change its fill color if J4 through N4
contain
the
word "done" once. I have tried several things but cannot figure out.

conditional formating
 

This is in addition to the first two. This would be the last criterior. I
am creating a worksheet that lets me know if I have seen a patient twice aka
"help" and the doc has seen once "done". I am trying to color corridinate
the pt names in col a

"Rick Rothstein" wrote:

Is that in addition to the first two I gave you or in place of the first two
I gave you? Also, how many criteria will you ultimately want on that one
cell (there are limitations as to how many different conditional formats you
can have on a single range).

--
Rick (MVP - Excel)


"Brent" wrote in message
...
Thanks for the feedback. I got this to work, but I have one more
question.
Now I would like a4 to change to a different color if the following is
met.
row J4 thru N4 has "help" twice and "done" once. Is there a way to
combine
if both criteria have been met?

"Rick Rothstein" wrote:

Select A4 and then call up the Conditional Formatting "New Rule" dialog.
You
will need two rules and both of them will use the "Use a formula to
determine which cells to format" option in the "Select a Rule Type"
listing.
These are the two formulas you would use in the "Format values where this
formula is true" field (for each rule individually)...

=COUNTIF(J4:N4,"done")=1

=COUNTIF(J4:N4,"help")=2

--
Rick (MVP - Excel)


"Brent" wrote in message
...
I am using Excel 2007 and would like to create certain rules and cannot
figure out how to do using conditional format. Lets say I have a
typical
spreadsheet. Here is the example I am looking for. Lets say A4 needs
to
have a certain fill color if in J4 through n4 contains the words "help"
twice. Then I need A4 to change its fill color if J4 through N4
contain
the
word "done" once. I have tried several things but cannot figure out.

conditional formating
 

Try this formula for your 3rd rule...

=AND(COUNTIF(J4:N4,"help")=2,COUNTIF(J4:N4,"done") =1)

--
Rick (MVP - Excel)


"Brent" wrote in message
...
This is in addition to the first two. This would be the last criterior.
I
am creating a worksheet that lets me know if I have seen a patient twice
aka
"help" and the doc has seen once "done". I am trying to color corridinate
the pt names in col a

"Rick Rothstein" wrote:

Is that in addition to the first two I gave you or in place of the first
two
I gave you? Also, how many criteria will you ultimately want on that one
cell (there are limitations as to how many different conditional formats
you
can have on a single range).

--
Rick (MVP - Excel)


"Brent" wrote in message
...
Thanks for the feedback. I got this to work, but I have one more
question.
Now I would like a4 to change to a different color if the following is
met.
row J4 thru N4 has "help" twice and "done" once. Is there a way to
combine
if both criteria have been met?

"Rick Rothstein" wrote:

Select A4 and then call up the Conditional Formatting "New Rule"
dialog.
You
will need two rules and both of them will use the "Use a formula to
determine which cells to format" option in the "Select a Rule Type"
listing.
These are the two formulas you would use in the "Format values where
this
formula is true" field (for each rule individually)...

=COUNTIF(J4:N4,"done")=1

=COUNTIF(J4:N4,"help")=2

--
Rick (MVP - Excel)


"Brent" wrote in message
...
I am using Excel 2007 and would like to create certain rules and
cannot
figure out how to do using conditional format. Lets say I have a
typical
spreadsheet. Here is the example I am looking for. Lets say A4
needs
to
have a certain fill color if in J4 through n4 contains the words
"help"
twice. Then I need A4 to change its fill color if J4 through N4
contain
the
word "done" once. I have tried several things but cannot figure
out.