+ Reply to Thread
Results 1 to 4 of 4

SchoolWork - Coloring certain cells by text criteria

  1. #1
    Registered User
    Join Date
    02-09-2004
    Location
    Utah
    Posts
    10

    Lightbulb SchoolWork - Coloring certain cells by text criteria

    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)
    Attached Images Attached Images

  2. #2
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545
    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 to 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?

    Bruce
    Bruce
    The older I get, the better I used to be.
    USA

  3. #3
    Registered User
    Join Date
    02-09-2004
    Location
    Utah
    Posts
    10

    Lightbulb Almost Worked...and then it did!

    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.

  4. #4
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1