conditional formating

[Brent]

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.

 

[Bob Phillips]

Try CF formulae of

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

and

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

 

[Rick Rothstein]

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

 

[Brent]

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]

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

 

[Brent]

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]

Try this formula for your 3rd rule...

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