Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Dec 2009
    Location
    Edinburgh, Scotland
    Posts
    85
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Insert Rows - colour filled cells repeated

    Excel 2003

    If you do an Insert Row you get as expected a blank row. But if there are colour filled cells in the row they get repeated in the new row. I thought this might be an anomaly in Excel 2003 but I've checked Open Office and it does this too, so it seems a deliberate feature. Is there some way of switching it off?

    In my application this can be lived with as a colour clear routine exists in a macro but it seems so strange a feature.

    Thanks
    Rob

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    This remains the same in 2007/2010. However, I just tested this in 2007. If you have a spreadsheet with with alternate row formatting (see picture) you can control whether inserted rows pick up the formatting. If I select the blank row (2) and insert a new row I get the formatting from the row below. BUT if I select a formatted row (3), the inserted row will pick up the blank formatting from the row beneath it. On another note, if its' alternate row formatting you're after may I suggest doing via a conditional format. A formula such as =MOD(ROW(),2)=1 in the conditional formatting dialog box will give you alternate row formatting (on odd numbered rows) or =MOD(ROW(),2)=0 will give you alternate row formatting on even numbered rows. Or you can play with the formula to apply formatting to every 3rd, 4th etc rows. because its' conditional dependent on row numbering if new rows are inserted/deleted the formatting will automatically adjust.
    Attached Images Attached Images
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Star Lounger
    Join Date
    Dec 2009
    Location
    Edinburgh, Scotland
    Posts
    85
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Thanks WG, for the re-assurance that it isn't just me !

    I thought for one moment I could see where this is leading - the key I thought was the 'formatting' of the cells. That is the border and so on would be copied down, but no, it is just the in-fill colour. Weird, annoying and lacking in any purpose that I can see.

    Rob

Posting Permissions

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