conditional formating

  • Thread starter Thread starter Brent
  • Start date Start date
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.
 
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
 
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?
 
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).
 
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
 
Try this formula for your 3rd rule...

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