+ Reply to Thread
Results 1 to 7 of 7

Macro to Change Cell Background Color From White to No Fill

  1. #1
    Registered User
    Join Date
    01-06-2009
    Location
    NYC
    MS-Off Ver
    Excel 2003
    Posts
    4

    Macro to Change Cell Background Color From White to No Fill

    Hi Everyone,

    I am new here and have very limited VBA experience but extensive standard excel experience. We have some massive excel models in which my predecessor decided to format each cell's background as white as opposed to leaving the cells un-filled and hiding gridlines. Therefore the excel files are needlessly enormous. As such, because some columns and rows are other colors than white and it will take an inordinate amount of time to manually change every row and column, I need a macro that essentially says "change cells in this workbook that are formatted with a white background to have no fill".

    It is probably a super simple macro to create but I am having no luck with my searches. I am probably using incorrect language. Any help would be greatly appreciated. Thank you.

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Maybe

    Please Login or Register  to view this content.
    or using Find and replace via format (Same as pressing Ctrl + H > Options > change formats as required > click whole workbook)

    Please Login or Register  to view this content.
    VBA Noob
    Last edited by VBA Noob; 01-06-2009 at 06:48 PM.
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Registered User
    Join Date
    01-06-2009
    Location
    NYC
    MS-Off Ver
    Excel 2003
    Posts
    4
    Thank you! That works but it takes a very long time per worksheet.

    [EDIT] Trying the second one, thanks.

  4. #4
    Registered User
    Join Date
    01-06-2009
    Location
    NYC
    MS-Off Ver
    Excel 2003
    Posts
    4
    Thanks, the second one seems to work but how do I get it to cycle through each sheet in the workbook? Here is what I have:

    Please Login or Register  to view this content.
    Last edited by emcdemc; 01-06-2009 at 08:49 PM.

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    You were close. You can't select the cells without selecting the worksheet, but there's no need to do either.
    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Registered User
    Join Date
    01-06-2009
    Location
    NYC
    MS-Off Ver
    Excel 2003
    Posts
    4
    Perfect thank you!

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    You’re welcome. Would you please mark the thread as Solved?

    Click the Edit button on your first post in the thread

    Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes

+ 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