conditional formating

B

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

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
 
B

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?
 
R

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

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
 
R

Rick Rothstein

Try this formula for your 3rd rule...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top