Results 1 to 9 of 9
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2006
    Location
    Maryland, USA
    Posts
    677
    Thanks
    17
    Thanked 57 Times in 50 Posts

    More help with recorded macros

    Folks,
    Right now with the first 3 of an expected 150 two-page tables (to be done by this Friday), I am selecting the topic rows by clicking in the left margin while holding down Ctrl. I then apply the shading macro I recorded. Naturally, I"m looking for a way to skip or reduce about 20 keystrokes. I can use Find or the Styles pane to select all white colored text, but I don't see a way to change the selection from the text to the cell it is in. Also, the recorder doesn't record select all instances chosen from the in the styles pane or Find In Document (and select all) from the search dialog.

    Is it possible to record a macro that selects the white type and then expands the selection to the whole row/cell?

    Thanks,
    Pam

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Something like this maybe?

    Code:
    Sub ShadeRow()
    Selection.HomeKey wdStory
    With Selection
        With .Find
            .ClearFormatting
            .Font.Color = wdColorRed
            .Text = ""
            .Replacement.Text = ""
            .Forward = True
            .Wrap = wdFindStop
            .Format = True
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
        End With
        While .Find.Execute
            .MoveEnd wdRow
            .Shading.ForegroundPatternColor = wdColorGray10
            .Collapse wdCollapseEnd
        Wend
        .Find.ClearFormatting
    End With
    End Sub

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2006
    Location
    Maryland, USA
    Posts
    677
    Thanks
    17
    Thanked 57 Times in 50 Posts
    Thanks, Jefferson. That should work, just have to switch to my color, which is specified in rgb. I'll let you know how I make out.

    Pam

  5. #4
    WS Lounge VIP
    Join Date
    Mar 2006
    Location
    Maryland, USA
    Posts
    677
    Thanks
    17
    Thanked 57 Times in 50 Posts
    Hi, Jefferson,

    The code doesn't work. The cursor just sits there, and when I open the find dialog, there's no history of the search. I did change the shading color to rgb. Could that have affected the whole macro?

    Pam

  6. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Maybe the problem is the in find color match. I got a document today which used a shade of green I literally could not figure out a way to "find."

  7. #6
    WS Lounge VIP
    Join Date
    Mar 2006
    Location
    Maryland, USA
    Posts
    677
    Thanks
    17
    Thanked 57 Times in 50 Posts
    Reveal formatting (Shft F1) is usually a good bet.

    Today, I'm getting an error message: Invalid or unqualified reference. And it's pointing to the "While .Find.Execute" line. If I add "Selection" before .Find, the debug(ger?) says the same thing at the next line. Hmmm. Help.

  8. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Might be an extra End With somewhere above that line.

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2006
    Location
    Maryland, USA
    Posts
    677
    Thanks
    17
    Thanked 57 Times in 50 Posts
    Jefferson: Thanks for your help. On reading your code, I started thinking of doing the task serially instead of in bulk (the way I do it manually). I recorded a macro to do all the steps once. Then I found a do loop within another macro and put it into mine. Works great.

    Thanks again,
    Pam

    Code:
    Sub ShadeHdRows()
    '
    'Mostly recorded
    '
        Selection.Find.ClearFormatting
        Selection.Find.Font.Color = -603914241
        Selection.Find.Replacement.ClearFormatting
        With Selection.Find
            .Text = ""
            .Replacement.Text = ""
            .Forward = True
            .Wrap = wdFindContinue
            .Format = True
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
        End With
    Do
        Selection.Find.Execute
        If Selection.Find.Found = True Then
        Selection.SelectRow
        Selection.Shading.Texture = wdTextureNone
        Selection.Shading.ForegroundPatternColor = wdColorAutomatic
        Selection.Shading.BackgroundPatternColor = 5982208
        Selection.MoveRight Unit:=wdCharacter, Count:=1
    Else
        Exit Do
    End If
    Loop
    Selection.Find.ClearFormatting
    End Sub

  10. #9
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,506
    Thanks
    3
    Thanked 141 Times in 134 Posts
    I can never work out why Word records the find using a mixture of With Selection.Find and explicitly repeating Selection.Find
    This version of your code tidies up the odd way that Word has recorded your steps and also simplifies the loop somewhat

    Code:
    Sub ShadeHdRows2()  With Selection.Find
        .ClearFormatting
        .Font.Color = 255
        .Replacement.ClearFormatting
        .Text = ""
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = True
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
        .Execute
        Do While .Found
          Selection.SelectRow
          Selection.Shading.Texture = wdTextureNone
          Selection.Shading.ForegroundPatternColor = wdColorAutomatic
          Selection.Shading.BackgroundPatternColor = 5982208
          Selection.MoveRight Unit:=wdCharacter, Count:=1
          .Execute
        Loop
        .ClearFormatting
      End With
    End Sub
    Last edited by Andrew Lockton; 2011-07-03 at 20:34.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

Posting Permissions

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