none
How to toggle formatting in a row RRS feed

  • Question

  • Hi All.....

    WinXP, Excel 2010.........I have a spreadsheet with numerous columns, A:BU, some columns of which are colored background.

    I would like my macro to color the row of the selection with a background of light gray, and upon toggle, to return it to whatever combination of colored and non-colored cells it had before the toggle......even tho the selected cell might be changed to a different row now.

    senario:

    select A12 and initiate the macro......row 12 changes to gray background color

    select b6 and initiate macro......row 12 returns to backrounds it had before and row 6 changes to gray background color

    select a1 and whatever row was colored gray, returns to whatever it was originally and no other row is colored....this is the null position

    Help please, of course I can color a row's background, but can't get my arms around "saving" a row's formatting to re-instate it later.

    Thanks,

    Chuck, CABGx3


    Chuck, CABGx3

    Friday, November 28, 2014 1:33 AM

Answers

  • Hello Chuck, think I remember your handle from ten years ago!

    One way or another you need to store the current formats. You could read the specific formats you may need to reinstate, perhaps only each cell's interior color, and store as data. The data could be in stored equivalent cells in some hidden sheet. However since 2007 cell color formats could include multiple properties if using theme/scheme colors.

    A different approach for say 'undo format changes' is to copy the area and paste special formats to a similar sized area in a hidden sheet. To restore copy formats from the equivalent cell reference in the hidden sheet and pastespecial the formats back to same in the user's sheet.

    The main problem with either approach is if rows/columns are inserted/deleted between storing and restoring formats. There are ways to cover that with Names or trapping any such changes but a lot more work! Multiple font formats might also need conisdering


    • Marked as answer by CABGx3 Friday, November 28, 2014 6:34 PM
    Friday, November 28, 2014 10:15 AM
    Moderator

All replies

  • Hello Chuck, think I remember your handle from ten years ago!

    One way or another you need to store the current formats. You could read the specific formats you may need to reinstate, perhaps only each cell's interior color, and store as data. The data could be in stored equivalent cells in some hidden sheet. However since 2007 cell color formats could include multiple properties if using theme/scheme colors.

    A different approach for say 'undo format changes' is to copy the area and paste special formats to a similar sized area in a hidden sheet. To restore copy formats from the equivalent cell reference in the hidden sheet and pastespecial the formats back to same in the user's sheet.

    The main problem with either approach is if rows/columns are inserted/deleted between storing and restoring formats. There are ways to cover that with Names or trapping any such changes but a lot more work! Multiple font formats might also need conisdering


    • Marked as answer by CABGx3 Friday, November 28, 2014 6:34 PM
    Friday, November 28, 2014 10:15 AM
    Moderator
  • Hi Peter.......
    Thanks for the reply........
    Yeah, that was me back then, and here now still trying to learn Excel/VBA.
     
    I kinda figured it was like you said, "capture the existing formatting and restore later" kinda thing, but I was just ahoping for some kind of a new function thing that might do it for me.  I'm not able to keep up with all the latest things, so Excel can still actually do a couple of things that I don't know about yet (lol, possible even more than a couple)
     
    Anyway, thanks for your thoughts,
    Merry Christmas, and my best to you and yours,
     
    Chuck, CABGx3

    Chuck, CABGx3

    Friday, November 28, 2014 6:34 PM