+ Reply to Thread
Results 1 to 6 of 6

Excel 2007 : Conditional format of rows based on cell

  1. #1
    Registered User
    Join Date
    08-19-2010
    Location
    San Francisco, USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Conditional format of rows based on cell

    Hi all,

    I'm new to this forum and hoping someone can help me. I have a spreadsheet of hospital patient visit data. The A column is a unique identifier for the patient, the B column is the date of their visits, and other columns are additional data related to that particular visit. The data are kept sorted on patient ID number, then on visit date.

    Some patients have 2-3 visits, some have dozens.

    I know conditional formatting can apply shading to alternating rows by entering the following formula into a new formatting rule:
    =MOD(ROW(),2)=0

    This doesn't quite work for me, however. Instead, I would like to be able to easily tell when one patient's data ends, and the next begins.

    How do I get excel to enter a solid border (the length of the entire spreadsheet) if the data point in column A does not equal the data directly above/below it (i.e., it's a new patient)? Alternatively, how could I tell excel to shade the whole row light gray if the data points in A match, then change to no shading when a new patient appears, then change back to light gray when the next patient appears, and so on?

    Any suggestions or links to a tutorial would be very much appreciated!

  2. #2
    Forum Contributor
    Join Date
    07-01-2010
    Location
    United States, Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    116

    Re: Conditional format of rows based on cell

    Could you please attach a dummy workbook for people to view, it is much easier to help out when people can refer to something. It does not have to be the exact workbook, just a simplified version of what you have and what needs to be done.

    I can do what you are asking, but I just need to see what you are working with to get a better understanding.

  3. #3
    Registered User
    Join Date
    08-19-2010
    Location
    San Francisco, USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Conditional format of rows based on cell

    Hi there,

    Here's an example of a file like what I'm using. Ideally, I would like to know how to both insert a line as border when the patient (denoted by pid) changes, as well as how to change the color of the rows when a new patient appears, if that's possible as well. pid is the only variable that cannot be missing in these data; other fields can sometimes be missing.

    Thanks very much!

  4. #4
    Forum Contributor
    Join Date
    07-01-2010
    Location
    United States, Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    116

    Re: Conditional format of rows based on cell

    I am still looking over the boardering one, however, I did manage to figure out the grey fill. Highlight column A, then Click on Home-->Conditional Formattion-->New Rule-->Top/Bottom ranked values-->bottom 1 % (not of selected range).

    Let me keep looking at the boarder one for you!

  5. #5
    Registered User
    Join Date
    08-19-2010
    Location
    San Francisco, USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Conditional format of rows based on cell

    Wow, cool. It's close... What I'm seeing when I do that is that when the pid (unique patient identifier) changes, the first cell in the C column gets colored in.

    So for our first patient, 175001 in the example above, C2 gets colored (row 1 is the header, of course!). Then our next patient, 175009, appears on row 16, so C16 gets colored. 175010 appears on row 25, and correspondingly, cell C25 colors in. Very cool! But is there a way to do this for the whole row where they first appear? Maybe I've done something incorrectly? (I selected a bunch of rows excluding the header row when I created this conditional format - when I selected all, including the header, it didn't appear to work)

    If you can also figure out how to do a border, that would be much appreciated.

    Thanks so much in advance!

  6. #6
    Registered User
    Join Date
    08-19-2010
    Location
    San Francisco, USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Conditional format of rows based on cell

    Well, I just re-read your post, and *this* time actually followed your instructions!

    Instead of selecting rows, I selected column A, the patient identifier. This time it shaded (filled with conditional formatting color) all the cells containing the patient id 175001 (cells A2-A15), but all the following cells below for the remaining patients were the same color. Is there a way for excel to flip back and forth between shaded and not shaded as the patient id number changes? (ie, in the example above, cells A2-A15 would be gray, cells A15-24 would have no fill, A25-42 would be gray... etc)
    Last edited by pancakeonions; 08-20-2010 at 05:44 PM. Reason: clarity of terminology - highlighting, vs. shading, vs. selecting, etc

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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