SchoolWork - Coloring certain cells by text criteria

[Rick_B]

I am working on a master class-schedule for students in our school.
Depending on their program (Medical, Dental, or Pharmacy), they are
required to take some courses and not others.

Students names run down column A (A2:A11). Their program (Medical,
Dental, Pharmacy) is listed next to their name in column B. All of the
classes offered at the school are listed across row 1 (C1:R1).

Based on their respective program, I'd like to color gray, the cells of
classes not required for each student. Is there a way, short of
manually formatting each cell in the row?

Thanks. (see the screen shot -for a sample)


+-------------------------------------------------------------------+
|Filename: ExcelTip1.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=3659 |
+-------------------------------------------------------------------+

 

[swatsp0p]

Check out Format>Conditional Formatting...

cell C2:
Condition1: Formula is: B2="Dental" and set Pattern color to Gray

cell D2:

Condition1: Formula is: B2="Dental" and set Pattern color to Gray
Condition2: Formula is: B2="Pharmacy" and set Pattern color t
Gray

etc. using up to 3 conditions per cell. When done, copy C2:R2
highlight C3:R18, paste>special>Formats

Does this work for you?

Bruc

 

[Rick_B]

Thanks for your response, Bruce. That was a great idea.
I tried to use the formula as you posted, but it turned everythin
gray, regardless and auto-corrected my entry to something wier
(=B2=""Dental""").

Nonetheless, by typing in C2
Formula is: =IF($B2="Dental",TRUE,FALSE) and formatting pattern t
gray

and in D2
Formula is: =IF($B2="Dental",TRUE,FALSE) and formatting pattern t
gray
Formula is: =IF($B2="Pharmacy",TRUE,FALSE) and formatting pattern t
gray

...it works!

I would never have thought to try that otherwise -with the whol
"formula is" thing- so thanks again for your tip

 

[swatsp0p]

Thanks for the feedback. I, too, have experienced CF altering th
syntax of the entered formula. Generally, re-entering the correc
syntax a 2nd time causes it to 'stick'.

In any event, you got your desired results and that is the importan
thing. As in most cases, there is more than one way to accomplish
task in Excel.

Cheers!

Bruc