Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Oct 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Word Macro for applying Macro to all Cells in First Column

    Hi.

    I'm a newbie to using word macros. I have a question regarding a word macro that I created. I was wondering if there is a macro code that would allow me to apply that macro to each cell in the first column of each table excluding the first two rows? If you know of anything that could possibly make it work, your help would be greatly appreciated.

    Thanks!

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Yes, that's quite easy. Assuming your table has no merged cells, you could use a macro like:
    Code:
    Dim i As Long
    With Selection
      If .Information(wdWithInTable) = False Then Exit Sub
      With .Tables(1)
        If .Uniform = False Then Exit Sub
        For i = 3 To .Rows.Count
          .Cell(i, 1).Range.HighlightColorIndex = wdBrightGreen
        Next
      End With
    End With
    The above code applies bright green highlighting to the cells.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    macropod's code applies highlighting to text inside a table but not to the cell itself. If you wanted the cells to be shaded then the following code would provide for that alternative and act on every table (with no merged cells) in the document rather than just the one currently selected.
    Code:
      Dim aTbl As Table, aRng As Range, aCell As Cell
      For Each aTbl In ActiveDocument.Tables
        If aTbl.Uniform Then
          For Each aCell In aTbl.Columns(1).Cells
            If aCell.RowIndex > 2 Then
              aCell.Shading.ForegroundPatternColor = RGB(150, 120, 10)
            End If
          Next aCell
        End If
      Next aTbl
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #4
    New Lounger
    Join Date
    Oct 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by macropod View Post
    Yes, that's quite easy. Assuming your table has no merged cells, you could use a macro like:
    Code:
    Dim i As Long
    With Selection
      If .Information(wdWithInTable) = False Then Exit Sub
      With .Tables(1)
        If .Uniform = False Then Exit Sub
        For i = 3 To .Rows.Count
          .Cell(i, 1).Range.HighlightColorIndex = wdBrightGreen
        Next
      End With
    End With
    The above code applies bright green highlighting to the cells.

    Yes, there are no merged cells in my macro. I tried adding that code to the beginning of the following:

    With Selection.ParagraphFormat
    .LeftIndent = InchesToPoints(0.5)
    .RightIndent = InchesToPoints(0)
    .SpaceBefore = 0
    .SpaceBeforeAuto = False
    .SpaceAfter = 0
    .SpaceAfterAuto = False
    .LineSpacingRule = wdLineSpaceSingle
    .Alignment = wdAlignParagraphLeft
    .WidowControl = False
    .KeepWithNext = False
    .KeepTogether = False
    .PageBreakBefore = False
    .NoLineNumber = False
    .Hyphenation = True
    .FirstLineIndent = InchesToPoints(-0.5)
    .OutlineLevel = wdOutlineLevelBodyText
    .CharacterUnitLeftIndent = 0
    .CharacterUnitRightIndent = 0
    .CharacterUnitFirstLineIndent = 0
    .LineUnitBefore = 0
    .LineUnitAfter = 0
    .MirrorIndents = False
    .TextboxTightWrap = wdTightNone
    End With
    Selection.ParagraphFormat.TabStops.ClearAll
    ActiveDocument.DefaultTabStop = InchesToPoints(0.01)
    Selection.ParagraphFormat.TabStops.Add Position:=InchesToPoints(5), _
    Alignment:=wdAlignTabRight, Leader:=wdTabLeaderDots
    Selection.ParagraphFormat.TabStops.Add Position:=InchesToPoints(5.1), _
    Alignment:=wdAlignTabLeft, Leader:=wdTabLeaderSpaces
    Selection.TypeText Text:=vbTab
    End Sub

    My goal is to apply the above to each cell in the first column of each table (excluding the first 2 rows). It doesn't seem to work. The only result I'm seeing is a column is being added. I may be adding the code incorrectly. Could you please clarify? Thanks.

  5. #5
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Nia

    Your code is using the selection object (ie where the cursor is) so this requires the cell contents to be selected before yours can work.

    It would be a lot simpler to just change the paragraph style of the cell contents rather than applying all these local settings. Can we entice you to use a paragraph style for this formatting?

    Code:
    Sub gotohell()
      Dim aTbl As Table, aRng As Range, aCell As Cell
      For Each aTbl In ActiveDocument.Tables
        If aTbl.Uniform Then
          For Each aCell In aTbl.Columns(1).Cells
            If aCell.RowIndex > 2 Then
              aCell.Shading.ForegroundPatternColor = RGB(150, 120, 10)
              aCell.Range.Style = "Cellstylename"
            End If
          Next aCell
        End If
      Next aTbl
    End Sub
    If you want this to work on just the selected table(s) then change ActiveDocument to Selection
    Last edited by Andrew Lockton; 2014-10-31 at 04:38.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Two comments:
    1. Instead of trying to hard-format a range, overriding any Style definitions already there, you should define a Style with the required specifications. The macro can then become:
    Code:
    Dim i As Long
    With Selection
      If .Information(wdWithInTable) = False Then Exit Sub
      With .Tables(1)
        If .Uniform = False Then Exit Sub
        For i = 3 To .Rows.Count
          .Cell(i, 1).Range.Style = "MyStyle"
        Next
      End With
    End With
    where MyStyle is the Style name.

    The above still assumes the insertion point is somewhere in the table. As Andrew says, though, if you know which table in the document you want to process, you don't need to select anything. In that case, the code might be:
    Code:
    Dim i As Long
    With ActiveDocument.Tables(1)
      For i = 3 To .Rows.Count
        .Cell(i, 1).Range.Style = "MyStyle"
      Next
    End With
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    New Lounger
    Join Date
    Oct 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Not working....



    Thanks again!

    Nia

    Quote Originally Posted by macropod View Post
    Two comments:
    1. Instead of trying to hard-format a range, overriding any Style definitions already there, you should define a Style with the required specifications. The macro can then become:
    Code:
    Dim i As Long
    With Selection
      If .Information(wdWithInTable) = False Then Exit Sub
      With .Tables(1)
        If .Uniform = False Then Exit Sub
        For i = 3 To .Rows.Count
          .Cell(i, 1).Range.Style = "MyStyle"
        Next
      End With
    End With
    where MyStyle is the Style name.

    The above still assumes the insertion point is somewhere in the table. As Andrew says, though, if you know which table in the document you want to process, you don't need to select anything. In that case, the code might be:
    Code:
    Dim i As Long
    With ActiveDocument.Tables(1)
      For i = 3 To .Rows.Count
        .Cell(i, 1).Range.Style = "MyStyle"
      Next
    End With
    Last edited by Nia; 2014-11-04 at 09:12.

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    You could use a macro like:
    Code:
    Sub Demo()
    Application.ScreenUpdating = False
    Dim Tbl As Table, TblCll As Cell
    For Each Tbl In ActiveDocument.Tables
      For Each TblCll In Tbl.Range.Cells
        With TblCll
          If .RowIndex > 2 Then
            If .ColumnIndex = 1 Then
              .Range.Style = "MyStyle"
            End If
          End If
        End With
      Next
    Next
    Application.ScreenUpdating = True
    End Sub
    where 'MyStyle' is the name of a Style formatted with the required attributes.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Cross-posted at: http://www.vbaexpress.com/forum/show...der-Macro-Help
    For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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