Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Find/Replace Custom Formatting (Excel 2003 and 2007)

    Hi,

    This is a tricky one.

    I am looking for the best approach to updating cell formatting with new cell formatting. The find/replace option is really great, but I want to automate it to run across lots of sheets in large workbooks. The main factor I need help with is how to capture the find and replace formatting that the user must provide. For example, I need the dialog to open so the user can provide what formatting to search for and then provide what formatting to replace with, so the macro must activate the replace dialog and allow the capturing of the two formats to be stored in variables so the macro knows what to replace across many sheets.

    Hope this makes sense. Any ideas or work-arounds will be appreciated,
    TX
    Regards,
    Rudi

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Find/Replace Custom Formatting (Excel 2003 and 2007)

    Perhaps I a missing something, but Excel's find and replace does find and replace formatting across sheets, just select all the sheets before calling the dialog...

    Steve

  3. #3
    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: Find/Replace Custom Formatting (Excel 2003 and 2007)

    It sounds to me like you should be using styles. If you modify a style's format options, all cells with that style applied will pick them up. (just like in Word).
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find/Replace Custom Formatting (Excel 2003 and 2007)

    OK...that helps if I knew that. (Strangely, it did not occur to me...I think I was so focussed on the problem at the time!) TX

    Rory,

    I did consider Styles, and this is true, but will only work if there is already a style to update. The workbook is recieved from another source that has custom formatted the worksheets with other formatting we want to replace. Cheers
    Regards,
    Rudi

  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: Find/Replace Custom Formatting (Excel 2003 and 2007)

    Tell them to use Excel properly? <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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