Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Conditional Formatting (2007)

    I am putting conditional formatting into a template, and it isn't quite working in the documents I create from that template.

    For most of my spreadsheet I have two rules: the first shades even rows in a range, and the second shades an entire column. This gets me a column with the second color in some areas, and stripes of both colors in others.

    At the bottom of my work area, I have almost the same rule layout: the first rule shades a specifically numbered row (outside of the range colored by the first rule) a third color, while the second rule still applies to the column.

    The final look is a column that is mostly of the second color, with stripes of the first color, and a single stripe at the bottom of the third color.

    That is what my template looks like.

    When I create a document from it, I enter data into those striped rows, and if there are too many blank rows I delete some so that the single row in the third color falls at the bottom of my work area.

    Here's the problem: when I delete those rows, I lose the dominance of the third color in its row, even though the conditional formatting still has it at the top of the list, and it continues to be painted properly in columns that are uncolored.

  2. #2
    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: Conditional Formatting (2007)

    Can you post a sample so we can see exactly what you are talking about?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: Conditional Formatting (2007)

    Sorry about that.

    This is a work in progress, but basically if you go in and delete rows, say 34-39, then the formatting in what was row 40 will get goofed up, even though the rules look like they're in the same shape.

  4. #4
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: Conditional Formatting (2007)

    I don't see the file - think I got caught by the filter.

    What I'm sending is a workbook that shows the problem, but it seems to go back to the template it's generated from.
    Attached Files Attached Files

  5. #5
    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: Conditional Formatting (2007)

    When you delete the rows, the row is no longer row 41 so doesn't get coloured by the conditional formatting. Change the rule to =ROW()=ROW() and it should work.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: Conditional Formatting (2007)

    Thanks a bunch Rory.

    I feel dumb about not seeing the row number, but even if I'd known that I would have had to post here to get the idea for setting the row equal to itself.

Posting Permissions

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