Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autoformat Macro (Excel 2000)

    In the code below I have a range of cells selected and a macro to autoformat those cells with classic 2. How would I change the cell address range in the code to allow any cells I have highlighted to accept the same autoformat...In other words I guess I'm wanting the macro to run on the cells I have highlighted...no matter what cells that might be.

    Sub AutoformatAnyCellsHighlighted()

    ' Autoformat Macro
    ' Macro recorded 9/20/2004 by NMP
    Range("A1:F8").Select
    Selection.Autoformat Format:=xlRangeAutoFormatClassic2, Number:=True, Font _
    :=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
    End Sub

    Thank you,
    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Autoformat Macro (Excel 2000)

    Use 'Selection', delete the reference to the range in your macro, like this:

    Sub AutoformatSelection()
    Selection.Autoformat Format:=xlRangeAutoFormatClassic2, Number:=True, Font _
    :=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autoformat Macro (Excel 2000)

    Thank you VERY much John.

    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  4. #4
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autoformat Macro (Excel 2000)

    In addition to John's reply, the macro recorder can generate a lot of superfluous code, your procedure can be simplified even further.

    <code>Sub AutoformatAnyCellsHighlighted()</code>
    <code> Selection.AutoFormat Format:=xlRangeAutoFormatClassic2</code>
    <code>End Sub</code>

    You should also note that if only 1 cell is selected in the table then the formatting is applied to the whole table.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autoformat Macro (Excel 2000)

    Tony,

    Thank you for the extra info. You are right. Recording the macro does throw tons of stuff you don't need into the macro. Appreciate the extra help.

    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Autoformat Macro (Excel 2000)

    The additional items correspond to the choices that are available when you click on the Options button in the Autoformat Dialog.

    When you are looking at code recorded by macros, click in the word of the Method that is being used, such as the word 'Autoformat', and press F1 for Help information on that Method. The Help will show which parameters are Required, and which are Optional, and show the valid settings for each parameter. That way you can figure out what parameters can be discarded, and sometimes you find parameter settings that didn't get recorded but which you'd like to set.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autoformat Macro (Excel 2000)

    John,

    Thank you. I will remember that.

    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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