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 |
+-------------------------------------------------------------------+

--
Rick_B
------------------------------------------------------------------------
Rick_B's Profile: http://www.excelforum.com/member.php...fo&userid=5964
View this thread: http://www.excelforum.com/showthread...hreadid=391380

Check out FormatConditional 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 to
Gray

etc. using up to 3 conditions per cell. When done, copy C2:R2,
highlight C3:R18, pastespecialFormats

Does this work for you?

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=391380

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

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

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

...it works!

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


--
Rick_B
------------------------------------------------------------------------
Rick_B's Profile: http://www.excelforum.com/member.php...fo&userid=5964
View this thread: http://www.excelforum.com/showthread...hreadid=391380

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

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

Cheers!

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=391380