Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Highlight Row (2003)

    Is it possible to highlight a column and then go to blanks and everywhere in that column that has a blank to make that whole row a color - say light gray?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Highlight Row (2003)

    One possibility is to use conditional formatting:
    <UL><LI>Select the rows that you want to apply this to (or the entire worksheet).
    <LI>Select Format | Conditional Formatting...
    <LI>Select Formula Is from the first dropdown list.
    <LI>Let's say that the active cell is in row 2, and that you want to inspect the value in column G. Enter the following formula in the box to the right of the dropdown list:
    <code>
    =$G2=""
    </code>
    The $ in front of the column letter is important.
    <LI>Click the Format... button
    <LI>Apply the formatting you want, for example in the Pattern tab.
    Click OK twice.[/list]Another option would be a macro, but the above approach has the advantage that no code is involved (and hence the user doesn't have to enable macros)

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Highlight Row (2003)

    I used the conditional formatting but the row that gets highlighted is the row above the one that has the blank. I used =$D2="" and it comes to the blank in Column D and highlights the row above. Am I doing something wrong?

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Highlight Row (2003)

    If the active cell was in row 1 when you set up the conditional formatting, that would happen. The row number needs to be the same as that of the active cell at the time that you set up the CF.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Highlight Row (2003)

    The row number you use in the formula should be the row number of the cell that is the active cell when you select Format | Conditional Formatting...
    If you use a different row number, the highlighted rows will be shifted.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Highlight Row (2003)

    There are over 5000 rows. Do you mean I have to select each row that has a blank in column D and then do the conditional format? I was selecting the whole spreadsheet.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Highlight Row (2003)

    No, you don't have to select individual rows. You can select a number of rows, or the entire sheet. If you select the entire sheet, the active cell is most likely A1. In that case, the formula for the conditional format should be
    <code>
    =$D1=""
    </code>
    D is the column in which you want to detect blanks, and 1 is the row number of the active cell. Excel will automatically adjust this row number for the other rows.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Highlight Row (2003)

    Thanks - that worked. I was confused about where the active cell was.

Posting Permissions

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